<?php
declare (strict_types = 1);

namespace app\webadmin\controller;

use app\webadmin\BaseController;
use app\webadmin\validate\SjjyCheck;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Font;
use think\facade\Db;
use think\facade\View;
use \think\facade\Log;
use app\webadmin\model\Sjjy as sjjylist;
use think\exception\ValidateException;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Calculation\Statistical;
class Sjjy extends BaseController
{
    public function index()
    {
        if (request()->isAjax()) {
            $param = get_params();
            $where = array();
            if (!empty($param['keywords'])) {
                $where[] = ['id|nums|data1', 'like', '%' . $param['keywords'] . '%'];
            }
            $where[]=['type','=',1];
            $where[]=['uid','=',get_login_admin('id')];
            $rows = empty($param['limit']) ? get_config('app.page_size') : $param['limit'];
            $content = sjjylist::where($where)
                ->order('id asc')
                ->paginate($rows, false, ['query' => $param])->each(function ($item, $k) {
                    //$item['createtime']=date('Y-m-d H:i:s',$item['create_time']);
                    $item['data1']=sprint_data(floatval($item['data1']),2);
                    $item['data2']=sprint_data(floatval($item['data2']),2);
                    $item['data3']=sprint_data(floatval($item['data3']),2);
                    $item['data4']=sprint_data(floatval($item['data4']),2);
                    $item['data5']=sprint_data(floatval($item['data5']),2);
                    $item['data6']=sprint_data(floatval($item['data6']),2);
                    $item['data7']=sprint_data(floatval($item['data7']),2);
                    $item['data8']=sprint_data(floatval($item['data8']),2);
                    $item['rsd']=sprint_data(floatval($item['rsd']),3);
                    $item['sds']=sprint_data(floatval($item['sds']),3);
                    $item['avgs']=sprint_data(floatval($item['avgs']),3);

                    return $item;
                });
            return table_assign(0, '', $content);
        } else {
            return view();
        }
    }

    //数据检验10家
    public function index_ten()
    {
        if (request()->isAjax()) {
            $param = get_params();
            $where = array();
            if (!empty($param['keywords'])) {
                $where[] = ['id|nums|data1', 'like', '%' . $param['keywords'] . '%'];
            }
            $where[]=['type','=',2];
            $where[]=['uid','=',get_login_admin('id')];
            $rows = empty($param['limit']) ? get_config('app.page_size') : $param['limit'];
            $content = sjjylist::where($where)
                ->order('id asc')
                ->paginate($rows, false, ['query' => $param])->each(function ($item, $k) {
                    //$item['createtime']=date('Y-m-d H:i:s',$item['create_time']);
                    $item['data1']=sprint_data(floatval($item['data1']),2);
                    $item['data2']=sprint_data(floatval($item['data2']),2);
                    $item['data3']=sprint_data(floatval($item['data3']),2);
                    $item['data4']=sprint_data(floatval($item['data4']),2);
                    $item['data5']=sprint_data(floatval($item['data5']),2);
                    $item['data6']=sprint_data(floatval($item['data6']),2);
                    $item['data7']=sprint_data(floatval($item['data7']),2);
                    $item['data8']=sprint_data(floatval($item['data8']),2);
                    $item['rsd']=sprint_data(floatval($item['rsd']),3);
                    $item['sds']=sprint_data(floatval($item['sds']),3);
                    $item['avgs']=sprint_data(floatval($item['avgs']),3);

                    return $item;
                });
            return table_assign(0, '', $content);
        } else {
            return view();
        }
    }

    //清空数据
    public function delete_all(){
        // 软删除
        $param = get_params();
        $db=sjjylist::where('type',$param['type'])->where('uid',get_login_admin('id'))->delete();
        if($db){

            return to_assign(0, "删除成功");
        } else {
            return to_assign(1, "删除失败");
        }
    }

    //9家
    public function add_sjjy(){
        $param = get_params();
        if (request()->isAjax()) {
            if (!empty($param['id']) && $param['id'] > 0) {
                //修改
                try {
                    validate(SjjyCheck::class)->scene('edit')->check($param);
                } catch (ValidateException $e) {
                    // 验证失败 输出错误信息
                    return to_assign(1, $e->getError());
                }
                $param['update_time'] = time();
                $res = sjjylist::strict(false)->field(true)->update($param);
                if ($res) {
                    add_log('edit', $param['id'], $param);
                    return to_assign(0, "修改成功");
                }
                return to_assign();
            } else {
                //新增
                try {
                    validate(SjjyCheck::class)->scene('add')->check($param);
                } catch (ValidateException $e) {
                    // 验证失败 输出错误信息
                    return to_assign(1, $e->getError());
                }
                $param['type']=1;
                $param['create_time'] = time();
                $insertId = sjjylist::strict(false)->field(true)->insertGetId($param);
                if ($insertId) {
                    add_log('add', $insertId, $param);
                    return to_assign(0, "添加成功");
                }
                return to_assign();
            }
        }
        else{
            $id = isset($param['id']) ? $param['id'] : 0;
            if ($id > 0) {
                $wdx = sjjylist::where(['id' => $id])->find();
                if($wdx){
                }
                View::assign('data', $wdx);
            }
            View::assign('id', $id);
            return view();
        }
    }

    public function add_sjjy_ten(){
        $param = get_params();
        if (request()->isAjax()) {
            if (!empty($param['id']) && $param['id'] > 0) {
                //修改
                try {
                    validate(SjjyCheck::class)->scene('edit')->check($param);
                } catch (ValidateException $e) {
                    // 验证失败 输出错误信息
                    return to_assign(1, $e->getError());
                }
                $param['update_time'] = time();
                $res = sjjylist::strict(false)->field(true)->update($param);
                if ($res) {
                    add_log('edit', $param['id'], $param);
                    return to_assign(0, "修改成功");
                }
                return to_assign();
            } else {
                //新增
                try {
                    validate(SjjyCheck::class)->scene('add')->check($param);
                } catch (ValidateException $e) {
                    // 验证失败 输出错误信息
                    return to_assign(1, $e->getError());
                }
                $param['type']=2;
                $param['create_time'] = time();
                $insertId = sjjylist::strict(false)->field(true)->insertGetId($param);
                if ($insertId) {
                    add_log('add', $insertId, $param);
                    return to_assign(0, "添加成功");
                }
                return to_assign();
            }
        }
        else{
            $id = isset($param['id']) ? $param['id'] : 0;
            if ($id > 0) {
                $wdx = sjjylist::where(['id' => $id])->find();
                if($wdx){
                }
                View::assign('data', $wdx);
            }
            View::assign('id', $id);
            return view();
        }
    }

    //删除数据
    public function delete_sjjy(){
        //Db::name('sjjy')->where('id','>',0)->delete();
        $id = get_params("id");
        if(empty($id)){
            return to_assign(1, "id不能为空");
        }
        // 软删除
        $db=sjjylist::where('id',$id)->find();
        if($db){
            // 软删除
            $db->delete();
            add_log('delete', $id);
            return to_assign(0, "删除成功");
        } else {
            return to_assign(1, "删除失败");
        }
    }

    //开始计算均匀性9家
    public function start_sjjy(){
        $where=[];
        $where[]=['uid','=',get_login_admin('id')];
        $where[]=['type','=',1];
        //Log::info('start_sjjy');
        $sjjylist=sjjylist::where($where)->order('id asc')->select();
        $i=0;
        $sum_avgs=0;
        $avg_arr=[];

        foreach($sjjylist as $key=>$v){
            $i++;
            //$rsd=round(floatval($v['rsd']),3);
            $sds=floatval($v['sds']);
            $avgs11=floatval($v['avgs']);
            $sum_avgs+=$avgs11;
            $avg_arr[]=floatval($avgs11);
            //=SMALL(C3:J3,1)
            $data_arr=[floatval($v['data1']),floatval($v['data2']),floatval($v['data3']),floatval($v['data4']),floatval($v['data5']),floatval($v['data6']),floatval($v['data7']),floatval($v['data8'])];
            //Log::info('data_arr='.json_encode($data_arr));
            $x1=Statistical::SMALL($data_arr,1);
            $x2=Statistical::SMALL($data_arr,2);
            $x3=Statistical::SMALL($data_arr,3);
            $x4=Statistical::SMALL($data_arr,4);
            $x5=Statistical::SMALL($data_arr,5);
            $x6=Statistical::SMALL($data_arr,6);
            $x7=Statistical::SMALL($data_arr,7);
            $x8=Statistical::SMALL($data_arr,8);
            $sjjylist[$key]['x1']=sprint_data($x1,2);
            $sjjylist[$key]['x2']=sprint_data($x2,2);
            $sjjylist[$key]['x3']=sprint_data($x3,2);
            $sjjylist[$key]['x4']=sprint_data($x4,2);
            $sjjylist[$key]['x5']=sprint_data($x5,2);
            $sjjylist[$key]['x6']=sprint_data($x6,2);
            $sjjylist[$key]['x7']=sprint_data($x7,2);
            $sjjylist[$key]['x8']=sprint_data($x8,2);
            //=(D17-C17)/(I17-C17)
            $r1=($x2-$x1)/($x7-$x1);
            $sjjylist[$key]['r1']=sprint_data($r1,3);
            //=(J17-I17)/(J17-D17)
            $r2=($x8-$x7)/($x8-$x2);
            $sjjylist[$key]['r2']=sprint_data($r2,3);
            //r1< f(5%,8)
            //且
            //rn< f(5%,8)
            //无异常值
            if($r1<0.608 && $r2<0.608){
                $zt='正常';
            }else{
                $zt='<font color=red>有异常值</font>';
            }
            $sjjylist[$key]['zt']=$zt;
            //=C17-$N$3
            //第2个表的数值
            $cc1=sprint_data($x1-$avgs11,2);
            $cc2=sprint_data($x2-$avgs11,2);
            $cc3=sprint_data($x3-$avgs11,2);
            $cc4=sprint_data($x4-$avgs11,2);
            $cc5=sprint_data($x5-$avgs11,2);
            $cc6=sprint_data($x6-$avgs11,2);
            $cc7=sprint_data($x7-$avgs11,2);
            $cc8=sprint_data($x8-$avgs11,2);
            $sjjylist[$key]['cc1']=$cc1;
            $sjjylist[$key]['cc2']=$cc2;
            $sjjylist[$key]['cc3']=$cc3;
            $sjjylist[$key]['cc4']=$cc4;
            $sjjylist[$key]['cc5']=$cc5;
            $sjjylist[$key]['cc6']=$cc6;
            $sjjylist[$key]['cc7']=$cc7;
            $sjjylist[$key]['cc8']=$cc8;
            //=K30*O3
            $cctxt=sprint_data(2.126*$sds,2);
            //$cctxt=0.01;
            $sjjylist[$key]['cctxt']=$cctxt;

        }
        $avg=$sum_avgs/9;
        //Log::info('range33='.json_encode($avg_arr));
        $sd = sprint_data(Statistical::STDEV($avg_arr),4);
        //=L3/K3
        $rsd=sprint_data($sd/$avg*100,4);
        //狄克逊法检验实验室间可疑值
        //可疑值
        //=SMALL($N$3:$N$11,1)
        $kyz['kyz1']=Statistical::SMALL($avg_arr,1);
        $kyz['kyz2']=Statistical::SMALL($avg_arr,2);
        $kyz['kyz3']=Statistical::SMALL($avg_arr,3);
        $kyz['kyz4']=Statistical::SMALL($avg_arr,4);
        $kyz['kyz5']=Statistical::SMALL($avg_arr,5);
        $kyz['kyz6']=Statistical::SMALL($avg_arr,6);
        $kyz['kyz7']=Statistical::SMALL($avg_arr,7);
        $kyz['kyz8']=Statistical::SMALL($avg_arr,8);
        $kyz['kyz9']=Statistical::SMALL($avg_arr,9);
        //r1 =(D42-C42)/(J42-C42)
        $kyz['r1']=sprint_data(($kyz['kyz2']-$kyz['kyz1'])/($kyz['kyz8']-$kyz['kyz1']),3);
        //rn =(K42-J42)/(K42-D42)
        $kyz['rn']=sprint_data(($kyz['kyz9']-$kyz['kyz8'])/($kyz['kyz9']-$kyz['kyz2']),3);
        /*r1< f(5%,9)且rn< f(5%,9)无异常值*/
        if($kyz['r1']<0.564 && $kyz['rn']<0.564){
            $kyz['zt']='无异常';
        }else{
            $kyz['zt']='<font color=red>有异常</font>';
        }
        //mean =AVERAGE(C42:K42)
        $kzy_range=[$kyz['kyz1'],$kyz['kyz2'],$kyz['kyz3'],$kyz['kyz4'],$kyz['kyz5'],$kyz['kyz6'],$kyz['kyz7'],$kyz['kyz8'],$kyz['kyz9']];
        $kyz['mean']=sprint_data(Statistical::AVERAGE($kzy_range),3);
        //SD =STDEV(C42:K42)
        $kyz['sd']=sprint_data(Statistical::STDEV($kzy_range),8);
        //格拉布斯法检验实验室内异常值
        //=C42-$Q$42
        $ycz['ycz1']=$kyz['kyz1']-$kyz['mean'];
        $ycz['ycz2']=$kyz['kyz2']-$kyz['mean'];
        $ycz['ycz3']=$kyz['kyz3']-$kyz['mean'];
        $ycz['ycz4']=$kyz['kyz4']-$kyz['mean'];
        $ycz['ycz5']=$kyz['kyz5']-$kyz['mean'];
        $ycz['ycz6']=$kyz['kyz6']-$kyz['mean'];
        $ycz['ycz7']=$kyz['kyz7']-$kyz['mean'];
        $ycz['ycz8']=$kyz['kyz8']-$kyz['mean'];
        $ycz['ycz9']=$kyz['kyz9']-$kyz['mean'];
        //λ(0.05, 9) 2.215
        $ycz10=2.215;
        $ycz['ycz10']=$ycz10;
        //λ(a,n)*s =L46*R42
        $ycz11=sprint_data($ycz10*$kyz['sd'],4);
        $ycz['ycz11']=$ycz11;
        //科克伦法实验室间数据组等精度检验
        //=O3^2
        $jdjy_array=sjjylist::where($where)->order('id asc')->column('sds');
        //$sds_max=sjjylist::where($where)->order('id asc')->max('sds');
        //$sds_min=sjjylist::where($where)->order('id asc')->min('sds');
        $jdjy['sz1']=sprint_data(pow($jdjy_array[0],2),4);
        $jdjy['sz2']=sprint_data(pow($jdjy_array[1],2),4);
        $jdjy['sz3']=sprint_data(pow($jdjy_array[2],2),4);
        $jdjy['sz4']=sprint_data(pow($jdjy_array[3],2),4);
        $jdjy['sz5']=sprint_data(pow($jdjy_array[4],2),4);
        $jdjy['sz6']=sprint_data(pow($jdjy_array[5],2),4);
        $jdjy['sz7']=sprint_data(pow($jdjy_array[6],2),4);
        $jdjy['sz8']=sprint_data(pow($jdjy_array[7],2),4);
        $jdjy['sz9']=sprint_data(pow($jdjy_array[8],2),4);
        //=MAX(C50:K50)
        //Log::info('jdjy_array='.json_encode($jdjy_array));
        $jdjy_array2=[floatval($jdjy['sz1']),floatval($jdjy['sz2']),floatval($jdjy['sz3']),floatval($jdjy['sz4']),floatval($jdjy['sz5']),floatval($jdjy['sz6']),floatval($jdjy['sz7']),floatval($jdjy['sz8']),floatval($jdjy['sz9'])];
        $jdjy['max']=Statistical::MAX($jdjy_array2);//最大值
        $jdjy['sum']=sprint_data(getSum($jdjy_array2),4);//求和
        //=L50/M50 科克伦统计量C
        if($jdjy['sum']>0){
            $jdjy['c']=truncateDecimal($jdjy['max']/$jdjy['sum'],4);
        }else{
            $jdjy['c']=0;
        }
        //C(0.05，9，8) 0.2768
        $jdjy['c2']=0.2768;
        //C< C(0.05，9，8)
        //实验室间数据平均值等精度
        if($jdjy['c']<$jdjy['c2']){
            $jdjy['zt']='正常';
        }else{
            $jdjy['zt']='不满足';
        }
        //一致性检验
        //=MAX(N3:N11)
        //=MIN(N3:N11)
        $yzx['max']=sprint_data(Statistical::MAX($avg_arr),2);
        $yzx['min']=sprint_data(Statistical::MIN($avg_arr),2);
        //=O3
        $yzx['max_sds']=$jdjy_array[0];
        //=O7
        $yzx['min_sds']=$jdjy_array[4];
        //=COUNT(C3:J3)
        $yzx['count']=8;
        //=(D53-D54)/(((((E53^2)*(E55-1))+((E54^2)*(E55-1)))/((E55*2-2)))*((E55+E55)/(E55*E55)))^0.5
        $ttt1=pow($yzx['max_sds'],2);
        $ttt2=pow($yzx['min_sds'],2);
        $yzxss2=((((($ttt1)*($yzx['count']-1))+(($ttt2)*($yzx['count']-1)))/(($yzx['count']*2-2)))*(($yzx['count']+$yzx['count'])/($yzx['count']*$yzx['count'])));
        $yzxss21=pow($yzxss2,0.5);
        $yzx['yzxss']=($yzx['max']-$yzx['min'])/$yzxss21;
        //临界值 =TINV(0.05,E55*2-2)
        $ljz=Statistical::TINV(0.05,$yzx['count']*2-2);
        $yzx['ljz']=sprint_data($ljz,9);

        $html = '<table class="layui-table layui-table-form">';
        $html .= "<tr><td align=\"center\">平均值</td><td>SD</td><td>RSD(%)</td></tr>";

        //
        if ($sjjylist) {
            $html .= "<tr><td align=\"center\">{$avg}</td><td>{$sd}</td><td>{$rsd}%</td></tr>";
        }
        $html.='</table>';
        $html.='<div style="margin-bottom: 20px"></div><br>';
        $html .= '<table class="layui-table layui-table-form">';
        $html .= "<tr><td align=\"center\">平均值</td><td>SD</td><td>RSD(%)</td></tr>";
        if ($sjjylist) {
            foreach ($sjjylist as $h) {
                $avgs=sprint_data(floatval($h['avgs']),3);
                $sds=sprint_data(floatval($h['sds']),3);
                $rsd=sprint_data(floatval($h['rsd']),3);
                $html .= "<tr><td>{$avgs}</td><td>{$sds}</td><td>{$rsd}</td></tr>";
            }
        }
        $html.='</table>';
        $html.='<div style="margin-top: 20px;margin-bottom: 20px"><h4>狄克逊法检验实验室内可疑值 r1< f<sub>(5%,8)</sub> && rn< f<sub>(5%,8)</sub>无异常值</h4></div><br>';
        $html.='<table class="layui-table layui-table-form">';
        $html .= "<tr><td align=\"center\">实验室</td><td>x<sub>(1)</sub></td><td>x<sub>(2)</sub></td><td align=\"center\"><sub>(3)</sub></td><td>x<sub>(4)</sub></td><td>x<sub>(5)</sub></td><td>x<sub>(6)</sub></td><td>x<sub>(7)</sub></td><td>x<sub>(8)</sub></td><td>r<sub>1</sub></td><td>r<sub>n</sub></td><td>f<sub>(0.05,8)</sub></td><td>判断</td></tr>";

        if ($sjjylist) {
            foreach ($sjjylist as $h) {
             $html .= "<tr><td align=\"center\">{$h['nums']}</td><td>{$h['x1']}</td><td>{$h['x2']}</td><td align=\"center\">{$h['x3']}</td><td>{$h['x4']}</td><td>{$h['x5']}</td><td>{$h['x6']}</td><td>{$h['x7']}</td><td>{$h['x8']}</td><td>{$h['r1']}</td><td>{$h['r2']}</td><td>0.608</td><td>{$h['zt']}</td></tr>";
            }
        }
        $html.='</table>';

        $html.='<div style="margin-top: 20px;margin-bottom: 20px"><h4>格拉布斯法检验实验室内异常值(测量值残差vi)(∣vi∣< λ<sub>(a,n)*s</sub>不符合要求的红色标明</h4></div><br>';
        $html.='<table class="layui-table layui-table-form">';
        $html .= "<tr><td align=\"center\">实验室</td><td>1</td><td>2</td><td align=\"center\">3</td><td>4</td><td>5</td><td>6</td><td>7</td><td>8</td><td>λ<sub>(0.05,8)</sub></td><td>λ<sub>(a,n)*s</sub></td><td>判断</td></tr>";

        if ($sjjylist) {
            foreach ($sjjylist as $h) {
                if($h['cc1']>$h['cctxt'] || $h['cc2']>$h['cctxt'] || $h['cc3']>$h['cctxt'] || $h['cc4']>$h['cctxt'] || $h['cc5']>$h['cctxt'] || $h['cc6']>$h['cctxt'] || $h['cc7']>$h['cctxt'] || $h['cc8']>$h['cctxt']){
                    $isyc='<font color=red>有异常</font>';
                }else{
                    $isyc='正常';
                }
                $html .= "<tr><td align=\"center\">{$h['nums']}</td><td>{$h['cc1']}</td><td>{$h['cc2']}</td><td align=\"center\">{$h['cc3']}</td><td>{$h['cc4']}</td><td>{$h['cc5']}</td><td>{$h['cc6']}</td><td>{$h['cc7']}</td><td>{$h['cc8']}</td><td>2.126</td><td>{$h['cctxt']}</td><td>{$isyc}</td></tr>";
            }
        }
        $html.='</table>';

        $html.='<div style="margin-top: 20px;margin-bottom: 20px"><h4>狄克逊法检验实验室间可疑值 (r1< f<sub>(5%,9)</sub>且rn< f<sub>(5%,9)</sub>无异常值)</h4></div><br>';
        $html.='<table class="layui-table layui-table-form">';
        $html .= "<tr><td align=\"center\">¯x<sub>(1)</sub></td><td>¯x<sub>(2)</sub></td><td>¯x<sub>(3)</sub></td><td align=\"center\">¯x<sub>(4)</sub></td><td>¯x<sub>(5)</sub></td><td>¯x<sub>(6)</sub></td><td>¯x<sub>(7)</sub></td><td>¯x<sub>(8)</sub></td><td>¯x<sub>(9)</sub></td><td>r<sub>1</sub></td><td>r<sub>n</sub></td><td>f<sub>(0.05,9)</sub></td><td>判断</td><td>mean</td><td>SD</td></tr>";
        $html .= "<tr><td align=\"center\">{$kyz['kyz1']}</td><td>{$kyz['kyz2']}</td><td>{$kyz['kyz3']}</td><td align=\"center\">{$kyz['kyz4']}</td><td>{$kyz['kyz5']}</td><td>{$kyz['kyz6']}</td><td>{$kyz['kyz7']}</td><td>{$kyz['kyz8']}</td><td>{$kyz['kyz9']}</td><td>{$kyz['r1']}</td><td>{$kyz['rn']}</td><td>0.564</td><td>{$kyz['zt']}</td><td>{$kyz['mean']}</td><td>{$kyz['sd']}</td></tr>";
        $html.='</table>';
        //格拉布斯法检验实验室内异常值
        $html.='<div style="margin-top: 20px;margin-bottom: 20px"><h4>格拉布斯法检验实验室内异常值 检验9家实验室间数据 测量值残差vi(∣vi∣< λ<sub>(a n)*s)</sub></h4></div><br>';
        $html.='<table class="layui-table layui-table-form">';
        $html .= "<tr><td align=\"center\">1</td><td>2</td><td>3</td><td align=\"center\">4</td><td>5</td><td>6</td><td>7</td><td>8</td><td>9</td><td>λ<sub>(0.05,9)</sub></td><td>λ<sub>(a,n)*s</sub></td><td>判断</td></tr>";
        $html .= "<tr><td align=\"center\">{$ycz['ycz1']}</td><td>{$ycz['ycz2']}</td><td>{$ycz['ycz3']}</td><td align=\"center\">{$ycz['ycz4']}</td><td>{$ycz['ycz5']}</td><td>{$ycz['ycz6']}</td><td>{$ycz['ycz7']}</td><td>{$ycz['ycz8']}</td><td>{$ycz['ycz9']}</td><td>{$ycz['ycz10']}</td><td>{$ycz['ycz11']}</td><td>∣vi∣< λ<sub>(a n)*s</sub></td></tr>";
        $html.='</table>';
        //科克伦法实验室间数据组等精度检验
        $html.='<div style="margin-top: 20px;margin-bottom: 20px"><h4>科克伦法实验室间数据组等精度检验(C< C<sub>(0.05，9，8)</sub>实验室间数据平均值等精度)</h4></div><br>';
        $html.='<table class="layui-table layui-table-form">';
        $html .= "<tr><td align=\"center\">S<sup>2</sup><sub>(1)</sub></td><td>S<sup>2</sup><sub>(2)</sub></td><td>S<sup>2</sup><sub>(3)</sub></td><td>S<sup>2</sup><sub>(4)</sub></td><td>S<sup>2</sup><sub>(5)</sub>)</td><td>S<sup>2</sup><sub>(6)</sub></td><td>S<sup>2</sup><sub>(7)</sub></td><td>S<sup>2</sup><sub>(8)</sub></td><td>S<sup>2</sup><sub>(9)</sub></td><td>s<sup>2</sup><sub>max</sub></td><td>实验室方差和</td><td>科克伦统计量C</td><td>C<sub>(0.05，9，8)</sub></td><td>判断</td></tr>";
        $html .= "<tr><td align=\"center\">{$jdjy['sz1']}</td><td>{$jdjy['sz2']}</td><td>{$jdjy['sz3']}</td><td>{$jdjy['sz4']}</td><td>{$jdjy['sz5']}</td><td>{$jdjy['sz6']}</td><td>{$jdjy['sz7']}</td><td>{$jdjy['sz8']}</td><td>{$jdjy['sz9']}</td><td>{$jdjy['max']}</td><td>{$jdjy['sum']}</td><td>{$jdjy['c']}</td><td>{$jdjy['c2']}</td><td>{$jdjy['zt']}</td></tr>";
        $html.='</table>';

        //一致性检验
        $html.='<div style="margin-top: 20px;margin-bottom: 20px"><h4>一致性检验 t-检验: 双样本等方差假设</h4></div><br>';
        $html.='<table class="layui-table layui-table-form">';
        $html .= "<tr><td align=\"center\"></td><td>平均值</td><td>SD</td><td>平均值一致性检验-t检验法</td><td>临界值</td></tr>";
        $html .= "<tr><td align=\"center\">最大值</td><td>{$yzx['max']}</td><td>{$yzx['max_sds']}</td><td></td><td></td></tr>";
        $html .= "<tr><td align=\"center\">最小值</td><td>{$yzx['min']}</td><td>{$yzx['min_sds']}</td><td></td><td></td></tr>";
        $html .= "<tr><td align=\"center\"></td><td>实验数</td><td>{$yzx['count']}</td><td>{$yzx['yzxss']}</td><td>{$yzx['ljz']}</td></tr>";
        $html.='</table>';
        //偏态系数和峰态系数法
        $html.='<div style="margin-top: 20px;margin-bottom: 20px"><h4>偏态系数和峰态系数法 正态分布检验</h4></div><br>';
        $html.='<table class="layui-table layui-table-form">';
        $html .= "<tr><td align=\"center\">残差</td><td>实验室</td><td>1</td><td>2</td><td>3</td><td>4</td><td>5</td><td>6</td><td>7</td><td>8</td></tr>";
        $fb_array=[];
        if ($sjjylist) {
            foreach ($sjjylist as $key=> $h) {
                $jy1=$h['data1']-$avg;
                $jy2=$h['data2']-$avg;
                $jy3=$h['data3']-$avg;
                $jy4=$h['data4']-$avg;
                $jy5=$h['data5']-$avg;
                $jy6=$h['data6']-$avg;
                $jy7=$h['data7']-$avg;
                $jy8=$h['data8']-$avg;
                $fb_array[]=$jy1;
                $fb_array[]=$jy2;
                $fb_array[]=$jy3;
                $fb_array[]=$jy4;
                $fb_array[]=$jy5;
                $fb_array[]=$jy6;
                $fb_array[]=$jy7;
                $fb_array[]=$jy8;
                //
                $lf1=pow($jy1,3);
                $lf2=pow($jy2,3);
                $lf3=pow($jy3,3);
                $lf4=pow($jy4,3);
                $lf5=pow($jy5,3);
                $lf6=pow($jy6,3);
                $lf7=pow($jy7,3);
                $lf8=pow($jy8,3);
                $sjjylist[$key]['lf1']=sprint_data($lf1,5);
                $sjjylist[$key]['lf2']=sprint_data($lf2,5);
                $sjjylist[$key]['lf3']=sprint_data($lf3,5);
                $sjjylist[$key]['lf4']=sprint_data($lf4,5);
                $sjjylist[$key]['lf5']=sprint_data($lf5,5);
                $sjjylist[$key]['lf6']=sprint_data($lf6,5);
                $sjjylist[$key]['lf7']=sprint_data($lf7,5);
                $sjjylist[$key]['lf8']=sprint_data($lf8,5);
                //
                $jy11=sprint_data($jy1,4);
                $jy21=sprint_data($jy2,4);
                $jy31=sprint_data($jy3,4);
                $jy41=sprint_data($jy4,4);
                $jy51=sprint_data($jy5,4);
                $jy61=sprint_data($jy6,4);
                $jy71=sprint_data($jy7,4);
                $jy81=sprint_data($jy8,4);

                //
                $lb1=pow($jy1,4);
                $lb2=pow($jy2,4);
                $lb3=pow($jy3,4);
                $lb4=pow($jy4,4);
                $lb5=pow($jy5,4);
                $lb6=pow($jy6,4);
                $lb7=pow($jy7,4);
                $lb8=pow($jy8,4);
                $sjjylist[$key]['lb1']=sprint_data($lb1,5);
                $sjjylist[$key]['lb2']=sprint_data($lb2,5);
                $sjjylist[$key]['lb3']=sprint_data($lb3,5);
                $sjjylist[$key]['lb4']=sprint_data($lb4,5);
                $sjjylist[$key]['lb5']=sprint_data($lb5,5);
                $sjjylist[$key]['lb6']=sprint_data($lb6,5);
                $sjjylist[$key]['lb7']=sprint_data($lb7,5);
                $sjjylist[$key]['lb8']=sprint_data($lb8,5);

                $html .= "<tr><td align=\"center\"></td><td>{$h['nums']}</td><td>{$jy11}</td><td>{$jy21}</td><td>{$jy31}</td><td>{$jy41}</td><td>{$jy51}</td><td>{$jy61}</td><td>{$jy71}</td><td>{$jy81}</td></tr>";
            }
        }
        //Log::info('fb_array='.json_encode($fb_array));
        $sum_fb=sum_pingfang($fb_array);
        $count_fb=count($fb_array);
        $fb_m2=sprint_data($sum_fb/$count_fb,4);
        //Log::info('sum_fb='.$sum_fb);
        //=SUMSQ(C61:J69)/COUNT(C61:J69)
        $html .= "<tr><td align=\"center\"></td><td>m<sub>2</sub></td><td>{$fb_m2}</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>";
        $html .= "<tr><td align=\"center\"></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>";
        //
        $html .= "<tr><td align=\"center\"></td><td align=\"center\">Lab</td><td>1</td><td>2</td><td>3</td><td>4</td><td>5</td><td>6</td><td>7</td><td>8</td></tr>";
        $fb2_array=[];
        if ($sjjylist) {
            foreach ($sjjylist as $key => $h) {
                $fb2_array[]=$h['lf1'];
                $fb2_array[]=$h['lf2'];
                $fb2_array[]=$h['lf3'];
                $fb2_array[]=$h['lf4'];
                $fb2_array[]=$h['lf5'];
                $fb2_array[]=$h['lf6'];
                $fb2_array[]=$h['lf7'];
                $fb2_array[]=$h['lf8'];
                $html .= "<tr><td align=\"center\"></td><td align=\"center\">{$h['nums']}</td><td>{$h['lf1']}</td><td>{$h['lf2']}</td><td>{$h['lf3']}</td><td>{$h['lf4']}</td><td>{$h['lf5']}</td><td>{$h['lf6']}</td><td>{$h['lf7']}</td><td>{$h['lf8']}</td></tr>";
            }
        }
        //=SUM(C73:J81)/COUNT(C73:J81)
        $sum_m3=sprint_data(sum_array($fb2_array)/count($fb2_array),9);
        $html .= "<tr><td align=\"center\"></td><td>m<sub>3</sub></td><td>{$sum_m3}</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>";
        $html .= "<tr><td align=\"center\"></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>";

        $html .= "<tr><td align=\"center\"></td><td align=\"center\">Lab</td><td>1</td><td>2</td><td>3</td><td>4</td><td>5</td><td>6</td><td>7</td><td>8</td></tr>";

        $lb1_array=[];
        if ($sjjylist) {
            foreach ($sjjylist as $key => $h) {
                $lb1_array[]=$h['lb1'];
                $lb1_array[]=$h['lb2'];
                $lb1_array[]=$h['lb3'];
                $lb1_array[]=$h['lb4'];
                $lb1_array[]=$h['lb5'];
                $lb1_array[]=$h['lb6'];
                $lb1_array[]=$h['lb7'];
                $lb1_array[]=$h['lb8'];
                $html .= "<tr><td align=\"center\"></td><td align=\"center\">{$h['nums']}</td><td>{$h['lb1']}</td><td>{$h['lb2']}</td><td>{$h['lb3']}</td><td>{$h['lb4']}</td><td>{$h['lb5']}</td><td>{$h['lb6']}</td><td>{$h['lb7']}</td><td>{$h['lb8']}</td></tr>";
            }
        }
        //=SUM(C85:J93)/COUNT(C85:J93)
        $sum_m4=sum_array($lb1_array)/count($lb1_array);
        $html .= "<tr><td align=\"center\"></td><td>m<sub>4</sub></td><td>{$sum_m4}</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>";
        $html .= "<tr><td align=\"center\"></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>";

        $html .= "<tr><td align=\"center\"></td><td></td><td></td><td>临界值（0.95,72）</td><td>判断</td><td>结论</td><td></td><td></td><td></td><td></td></tr>";
        //=ABS(C82)/SQRT(POWER(C70,3))
        $ptxsa=sprint_data(abs(floatval($sum_m3))/sqrt(pow(floatval($fb_m2),3)),4);
        if($ptxsa<0.46){
            $psxzt='正态分布';
        }else{
            $psxzt='有异常';
        }
        //=C94/POWER(C70,2)
        $ftxsb=sprint_data($sum_m4/pow($fb_m2,2),4);
        if($ftxsb>2.15 && $ftxsb<3.99){
            $ftxzt='正态分布';
        }else{
            $ftxzt='<font color=red>有异常</font>';
        }
        $html .= "<tr><td align=\"center\"></td><td>偏态系数A</td><td>{$ptxsa}</td><td>0.46</td><td>A&lt;A1</td><td>{$psxzt}</td><td></td><td></td><td></td><td></td></tr>";
        $html .= "<tr><td align=\"center\"></td><td>峰态系数B</td><td>{$ftxsb}</td><td>2.15-3.99</td><td>(B1&lt;B&lt;B1)</td><td>{$ftxzt}</td><td></td><td></td><td></td><td></td></tr>";

        $html.='</table>';
        echo $html;
    }

    //export_data9家
    public function export_data(){

        $where=[];
        $where[]=['uid','=',get_login_admin('id')];
        $where[]=['type','=',1];
        $sjjylist=sjjylist::where($where)->order('id asc')->select();
        $i=0;
        $sum_avgs=0;
        $avg_arr=[];

        foreach($sjjylist as $key=>$v){
            $i++;
            //$rsd=round(floatval($v['rsd']),3);
            $sds=floatval($v['sds']);
            $avgs11=floatval($v['avgs']);
            $sum_avgs+=$avgs11;
            $avg_arr[]=floatval($avgs11);
            //=SMALL(C3:J3,1)
            $data_arr=[floatval($v['data1']),floatval($v['data2']),floatval($v['data3']),floatval($v['data4']),floatval($v['data5']),floatval($v['data6']),floatval($v['data7']),floatval($v['data8'])];
            //Log::info('data_arr='.json_encode($data_arr));
            $x1=Statistical::SMALL($data_arr,1);
            $x2=Statistical::SMALL($data_arr,2);
            $x3=Statistical::SMALL($data_arr,3);
            $x4=Statistical::SMALL($data_arr,4);
            $x5=Statistical::SMALL($data_arr,5);
            $x6=Statistical::SMALL($data_arr,6);
            $x7=Statistical::SMALL($data_arr,7);
            $x8=Statistical::SMALL($data_arr,8);
            $sjjylist[$key]['x1']=sprint_data($x1,2);
            $sjjylist[$key]['x2']=sprint_data($x2,2);
            $sjjylist[$key]['x3']=sprint_data($x3,2);
            $sjjylist[$key]['x4']=sprint_data($x4,2);
            $sjjylist[$key]['x5']=sprint_data($x5,2);
            $sjjylist[$key]['x6']=sprint_data($x6,2);
            $sjjylist[$key]['x7']=sprint_data($x7,2);
            $sjjylist[$key]['x8']=sprint_data($x8,2);
            //=(D17-C17)/(I17-C17)
            $r1=($x2-$x1)/($x7-$x1);
            $sjjylist[$key]['r1']=sprint_data($r1,3);
            //=(J17-I17)/(J17-D17)
            $r2=($x8-$x7)/($x8-$x2);
            $sjjylist[$key]['r2']=sprint_data($r2,3);
            //r1< f(5%,8)
            //且
            //rn< f(5%,8)
            //无异常值
            if($r1<0.608 && $r2<0.608){
                $zt='正常';
            }else{
                $zt='有异常值';
            }
            $sjjylist[$key]['zt']=$zt;
            //=C17-$N$3
            //第2个表的数值
            $cc1=sprint_data($x1-$avgs11,2);
            $cc2=sprint_data($x2-$avgs11,2);
            $cc3=sprint_data($x3-$avgs11,2);
            $cc4=sprint_data($x4-$avgs11,2);
            $cc5=sprint_data($x5-$avgs11,2);
            $cc6=sprint_data($x6-$avgs11,2);
            $cc7=sprint_data($x7-$avgs11,2);
            $cc8=sprint_data($x8-$avgs11,2);
            $sjjylist[$key]['cc1']=$cc1;
            $sjjylist[$key]['cc2']=$cc2;
            $sjjylist[$key]['cc3']=$cc3;
            $sjjylist[$key]['cc4']=$cc4;
            $sjjylist[$key]['cc5']=$cc5;
            $sjjylist[$key]['cc6']=$cc6;
            $sjjylist[$key]['cc7']=$cc7;
            $sjjylist[$key]['cc8']=$cc8;
            //=K30*O3
            $cctxt=sprint_data(2.126*$sds,2);
            //$cctxt=0.01;
            $sjjylist[$key]['cctxt']=$cctxt;

        }
        $avg=$sum_avgs/9;
        //Log::info('range33='.json_encode($avg_arr));
        $sd = sprint_data(Statistical::STDEV($avg_arr),4);
        //=L3/K3
        $rsd=sprint_data($sd/$avg*100,4);
        //狄克逊法检验实验室间可疑值
        //可疑值
        //=SMALL($N$3:$N$11,1)
        $kyz['kyz1']=Statistical::SMALL($avg_arr,1);
        $kyz['kyz2']=Statistical::SMALL($avg_arr,2);
        $kyz['kyz3']=Statistical::SMALL($avg_arr,3);
        $kyz['kyz4']=Statistical::SMALL($avg_arr,4);
        $kyz['kyz5']=Statistical::SMALL($avg_arr,5);
        $kyz['kyz6']=Statistical::SMALL($avg_arr,6);
        $kyz['kyz7']=Statistical::SMALL($avg_arr,7);
        $kyz['kyz8']=Statistical::SMALL($avg_arr,8);
        $kyz['kyz9']=Statistical::SMALL($avg_arr,9);
        //r1 =(D42-C42)/(J42-C42)
        $kyz['r1']=sprint_data(($kyz['kyz2']-$kyz['kyz1'])/($kyz['kyz8']-$kyz['kyz1']),3);
        //rn =(K42-J42)/(K42-D42)
        $kyz['rn']=sprint_data(($kyz['kyz9']-$kyz['kyz8'])/($kyz['kyz9']-$kyz['kyz2']),3);
        /*r1< f(5%,9)且rn< f(5%,9)无异常值*/
        if($kyz['r1']<0.564 && $kyz['rn']<0.564){
            $kyz['zt']='无异常';
        }else{
            $kyz['zt']='有异常';
        }
        //mean =AVERAGE(C42:K42)
        $kzy_range=[$kyz['kyz1'],$kyz['kyz2'],$kyz['kyz3'],$kyz['kyz4'],$kyz['kyz5'],$kyz['kyz6'],$kyz['kyz7'],$kyz['kyz8'],$kyz['kyz9']];
        $kyz['mean']=sprint_data(Statistical::AVERAGE($kzy_range),3);
        //SD =STDEV(C42:K42)
        $kyz['sd']=sprint_data(Statistical::STDEV($kzy_range),8);
        //格拉布斯法检验实验室内异常值
        //=C42-$Q$42
        $ycz['ycz1']=$kyz['kyz1']-$kyz['mean'];
        $ycz['ycz2']=$kyz['kyz2']-$kyz['mean'];
        $ycz['ycz3']=$kyz['kyz3']-$kyz['mean'];
        $ycz['ycz4']=$kyz['kyz4']-$kyz['mean'];
        $ycz['ycz5']=$kyz['kyz5']-$kyz['mean'];
        $ycz['ycz6']=$kyz['kyz6']-$kyz['mean'];
        $ycz['ycz7']=$kyz['kyz7']-$kyz['mean'];
        $ycz['ycz8']=$kyz['kyz8']-$kyz['mean'];
        $ycz['ycz9']=$kyz['kyz9']-$kyz['mean'];
        //λ(0.05, 9) 2.215
        $ycz10=2.215;
        $ycz['ycz10']=$ycz10;
        //λ(a,n)*s =L46*R42
        $ycz11=sprint_data($ycz10*$kyz['sd'],4);
        $ycz['ycz11']=$ycz11;
        //科克伦法实验室间数据组等精度检验
        //=O3^2
        $jdjy_array=sjjylist::where($where)->order('id asc')->column('sds');
        //$sds_max=sjjylist::where($where)->order('id asc')->max('sds');
        //$sds_min=sjjylist::where($where)->order('id asc')->min('sds');
        $jdjy['sz1']=sprint_data(pow($jdjy_array[0],2),4);
        $jdjy['sz2']=sprint_data(pow($jdjy_array[1],2),4);
        $jdjy['sz3']=sprint_data(pow($jdjy_array[2],2),4);
        $jdjy['sz4']=sprint_data(pow($jdjy_array[3],2),4);
        $jdjy['sz5']=sprint_data(pow($jdjy_array[4],2),4);
        $jdjy['sz6']=sprint_data(pow($jdjy_array[5],2),4);
        $jdjy['sz7']=sprint_data(pow($jdjy_array[6],2),4);
        $jdjy['sz8']=sprint_data(pow($jdjy_array[7],2),4);
        $jdjy['sz9']=sprint_data(pow($jdjy_array[8],2),4);
        //=MAX(C50:K50)
        //Log::info('jdjy_array='.json_encode($jdjy_array));
        $jdjy_array2=[floatval($jdjy['sz1']),floatval($jdjy['sz2']),floatval($jdjy['sz3']),floatval($jdjy['sz4']),floatval($jdjy['sz5']),floatval($jdjy['sz6']),floatval($jdjy['sz7']),floatval($jdjy['sz8']),floatval($jdjy['sz9'])];
        $jdjy['max']=Statistical::MAX($jdjy_array2);//最大值
        $jdjy['sum']=sprint_data(getSum($jdjy_array2),4);//求和
        //=L50/M50 科克伦统计量C
        if($jdjy['sum']>0){
            $jdjy['c']=sprint_data($jdjy['max']/$jdjy['sum'],4);
        }else{
            $jdjy['c']=0;
        }
        //C(0.05，9，8) 0.2768
        $jdjy['c2']=0.2768;
        //C< C(0.05，9，8)
        //实验室间数据平均值等精度
        if($jdjy['c']<$jdjy['c2']){
            $jdjy['zt']='正常';
        }else{
            $jdjy['zt']='不满足';
        }
        //一致性检验
        //=MAX(N3:N11)
        //=MIN(N3:N11)
        $yzx['max']=sprint_data(Statistical::MAX($avg_arr),2);
        $yzx['min']=sprint_data(Statistical::MIN($avg_arr),2);
        //=O3
        $yzx['max_sds']=$jdjy_array[0];
        //=O7
        $yzx['min_sds']=$jdjy_array[4];
        //=COUNT(C3:J3)
        $yzx['count']=8;
        //=(D53-D54)/(((((E53^2)*(E55-1))+((E54^2)*(E55-1)))/((E55*2-2)))*((E55+E55)/(E55*E55)))^0.5
        $ttt1=pow($yzx['max_sds'],2);
        $ttt2=pow($yzx['min_sds'],2);
        $yzxss2=((((($ttt1)*($yzx['count']-1))+(($ttt2)*($yzx['count']-1)))/(($yzx['count']*2-2)))*(($yzx['count']+$yzx['count'])/($yzx['count']*$yzx['count'])));
        $yzxss21=pow($yzxss2,0.5);
        $yzx['yzxss']=($yzx['max']-$yzx['min'])/$yzxss21;
        //临界值 =TINV(0.05,E55*2-2)
        $ljz=Statistical::TINV(0.05,$yzx['count']*2-2);
        $yzx['ljz']=sprint_data($ljz,9);


        //导出表格
        $objExcel = new Spreadsheet();
        $objWriter = IOFactory::createWriter($objExcel, 'Xlsx');
        $objActSheet = $objExcel->getActiveSheet(0);
        $objActSheet->setTitle('数据检验9家分析');//设置excel的标题

        $objActSheet->getRowDimension(1)->setRowHeight(25);
        //$objActSheet->getStyle('A1:F1')->getFont()->setSize(14);
        //$objActSheet->getStyle('A1:F1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('CCCCCC');
        // 设置列宽
        $objActSheet->getColumnDimension('A')->setWidth(15);
        $objActSheet->getColumnDimension('B')->setWidth(15);
        $objActSheet->getColumnDimension('C')->setWidth(15);
        $objActSheet->getColumnDimension('D')->setWidth(15);
        $objActSheet->getColumnDimension('E')->setWidth(15);
        $objActSheet->getColumnDimension('F')->setWidth(15);
        $objActSheet->getColumnDimension('G')->setWidth(15);
        $objActSheet->getColumnDimension('H')->setWidth(15);
        $objActSheet->getColumnDimension('I')->setWidth(15);
        $objActSheet->getColumnDimension('J')->setWidth(15);
        $objActSheet->getColumnDimension('K')->setWidth(15);
        $objActSheet->getColumnDimension('L')->setWidth(15);
        $objActSheet->getColumnDimension('M')->setWidth(15);
        $objActSheet->getColumnDimension('N')->setWidth(15);
        // 设置行高

        //设置水平、垂直居中
        $styleArray2 = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];
        $styleArray3 = [
            'font' => [
                'name' => 'Arial',
                'bold' => true,
                'italic' => false,
                'underline' => false,
                'strikethrough' => false,
                'superscript' => true, // 设置为下标
                'color' => [
                    'rgb' => Color::COLOR_BLACK
                ]
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];
        //样式变量
        $styleArray = [
        //设置字体样式
            'font' => [
                'name' => 'Arial',
                //'bold' => true,
                //'italic' => false,
                //'underline' => Font::UNDERLINE_DOUBLE,
                //'strikethrough' => false,
                'color' => [
                    'rgb' => Color::COLOR_BLACK
                ]
            ],
            //设置边框线样式
            'borders' => [
                //allBorders所有的边框线样式
                //左边框线
                /*'bottom' => [
                    'borderStyle' => Border::BORDER_DASHDOT,
                    'color' => [
                        'rgb' => '808080'
                    ]
                ],*/
                //上边框线
                /*'top' => [
                    'borderStyle' => Border::BORDER_DASHDOT,
                    'color' => [
                        'rgb' => '808080'
                    ]
                ]*/
            ],
            //对齐样式
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
                'wrapText' => true,
            ],
            //是否使用前缀
            'quotePrefix'    => true
        ];

        $objActSheet->getStyle('A1:F10')->applyFromArray($styleArray);
        $objActSheet->setCellValue('A1', '平均值');
        $objActSheet->setCellValue('B1', 'SD');
        $objActSheet->setCellValue('C1', 'RSD(%)');
        $objActSheet->setCellValue('D1', '平均值');
        $objActSheet->setCellValue('E1', 'SD');
        $objActSheet->setCellValue('F1', 'RSD(%)');
        $objActSheet->getStyle('A1'  . ':F10')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式

        $baseRow = 2; //数据从N-1行开始往下输出
        foreach ($sjjylist as $r => $h) {
            $avgs11=sprint_data(floatval($h['avgs']),3);
            $sds11=sprint_data(floatval($h['sds']),3);
            $rsd11=sprint_data(floatval($h['rsd']),3);
            $i = $baseRow + $r;
            $objActSheet->getRowDimension($i)->setRowHeight(25);
            $objActSheet->getStyle('A' . $i . ':C' . $i)->getFont()->setSize(12);
            $objActSheet->setCellValue('A' . $i, '');
            $objActSheet->setCellValue('B' . $i, '');
            $objActSheet->setCellValue('C' . $i, '');
            $objActSheet->setCellValue('D' . $i, $avgs11);
            $objActSheet->setCellValue('E' . $i, $sds11);
            $objActSheet->setCellValue('F' . $i, $rsd11);
        }
        // 合并单元格
        $objActSheet->mergeCells('A2' .':A10');            // 合并单元格
        $objActSheet->mergeCells('B2' .':B10');            // 合并单元格
        $objActSheet->mergeCells('C2' .':C10');
        // 设置合并后的单元格的值
        $objActSheet->setCellValue('A2', $avg);
        $objActSheet->setCellValue('B2', $sd);
        $objActSheet->setCellValue('C2', $rsd.'%');

        $objActSheet->getStyle('A15:M25')->applyFromArray($styleArray);

         //狄克逊法检验实验室内可疑值
        $objActSheet->mergeCells('A15' .':N15');
        $objActSheet->getStyle('A15')->getFont()->setSize(14);
        $objActSheet->getStyle('A15')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('CCCCCC');
        $objActSheet->setCellValue('A15', '狄克逊法检验实验室内可疑值');


        //$xb1='x<sub>(1)</sub>';
        $objActSheet->setCellValue('A16', '实验室');
        $objActSheet->setCellValue('B16', 'x(1)');
        $objActSheet->getStyle('B16')->getFont()->setSuperscript(true);

        $objActSheet->setCellValue('C16', 'x(2)');
        //$objActSheet->getStyle('C16')->getFont()->setSuperscript(false);
        //$objActSheet->getStyle('C16')->getFont()->setSubscript(true);
        $objActSheet->setCellValue('D16', 'x(3)');
        $objActSheet->setCellValue('E16', 'x(4)');
        $objActSheet->setCellValue('F16', 'x(5)');
        $objActSheet->setCellValue('G16', 'x(6)');
        $objActSheet->setCellValue('H16', 'x(7)');
        $objActSheet->setCellValue('I16', 'x(8)');
        $objActSheet->setCellValue('J16', 'r(1)');
        $objActSheet->setCellValue('K16', 'r(n)');
        $objActSheet->setCellValue('L16', 'f(0.05,8)');
        $objActSheet->setCellValue('M16', '判断');

        $objActSheet->getStyle('A16'  . ':N25')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
        //合并单元格
        $objActSheet->mergeCells('N16' .':N25');

        $objActSheet->setCellValue('N16', '如果不满足要求，则将不满足判断要求的异常值用红色显示');
        $objActSheet->getStyle('N16')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('FF0000');
        $objActSheet->getStyle('N16')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLACK);//将A1单元格文字颜色设为红色
        $objActSheet->getStyle('N16')->applyFromArray($styleArray2);
        //文本内容自动换行
        $objActSheet->getStyle('N16')->getAlignment()->setWrapText(true);

        if ($sjjylist) {
            $baseRow2 = 17; //数据从N-1行开始往下输出
            foreach ($sjjylist as $r => $h) {
                $i = $baseRow2 + $r;
                $objActSheet->getRowDimension($i)->setRowHeight(25);
                $objActSheet->setCellValue('A' . $i, $h['nums']);
                $objActSheet->setCellValue('B' . $i, $h['x1']);
                $objActSheet->setCellValue('C' . $i, $h['x2']);
                $objActSheet->setCellValue('D' . $i, $h['x3']);
                $objActSheet->setCellValue('E' . $i, $h['x4']);
                $objActSheet->setCellValue('F' . $i, $h['x5']);
                $objActSheet->setCellValue('G' . $i, $h['x6']);
                $objActSheet->setCellValue('H' . $i, $h['x7']);
                $objActSheet->setCellValue('I' . $i, $h['x8']);
                $objActSheet->setCellValue('J' . $i, $h['r1']);
                $objActSheet->setCellValue('K' . $i, $h['r2']);
                $objActSheet->setCellValue('L' . $i, '0.608');
                $objActSheet->setCellValue('M' . $i, $h['zt']);
                $objActSheet->setCellValue('N' . $i, '');
                //数据异常的显示红色
                if($h['r1']>0.608 ){
                    $objActSheet->getStyle('J'. $i)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
                }
                if($h['r2']>0.608){
                    $objActSheet->getStyle('K'. $i)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
                }
                if($h['zt']=='有异常值'){
                    $objActSheet->getStyle('M'. $i)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色

                }
            }
        }
        //格拉布斯法检验实验室内异常值
        //合并单元格
        $objActSheet->mergeCells('A28' .':M28');
        $objActSheet->getStyle('A28')->getFont()->setSize(14);
        $objActSheet->getStyle('A28')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('CCCCCC');
        $objActSheet->setCellValue('A28', '格拉布斯法检验实验室内异常值');
        $objActSheet->mergeCells('B29' .':I29');
        $objActSheet->mergeCells('L31' .':L39');
        $objActSheet->mergeCells('M29' .':M39');

        //测量值残差vi
        $objActSheet->setCellValue('B29', '测量值残差vi');

        //文本内容自动换行
        $objActSheet->getStyle('L31')->getAlignment()->setWrapText(true);
        $objActSheet->getStyle('M29')->getAlignment()->setWrapText(true);
        $objActSheet->setCellValue('M29', '如果不满足要求，则将不满足判断要求的异常值用红色显示');
        $objActSheet->getStyle('M29')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('FF0000');
        $objActSheet->getStyle('M29')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLACK);//将A1单元格文字颜色设为红色
        $objActSheet->getStyle('M29')->applyFromArray($styleArray2);

        $objActSheet->mergeCells('A29' .':A30');
        $objActSheet->mergeCells('J29' .':J30');
        $objActSheet->mergeCells('K29' .':K30');
        $objActSheet->mergeCells('L29' .':L30');


        $objActSheet->setCellValue('A29', '实验室');
        $objActSheet->setCellValue('J29', 'λ(0.05, 8)');
        $objActSheet->setCellValue('K29', 'λ(a，n)*s');
        $objActSheet->setCellValue('L29', '判断');

        $objActSheet->setCellValue('B30', '1');
        $objActSheet->setCellValue('C30', '2');
        $objActSheet->setCellValue('D30', '3');
        $objActSheet->setCellValue('E30', '4');
        $objActSheet->setCellValue('F30', '5');
        $objActSheet->setCellValue('G30', '6');
        $objActSheet->setCellValue('H30', '7');
        $objActSheet->setCellValue('I30', '8');

        $objActSheet->getStyle('A28'  . ':M39')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
        $objActSheet->getStyle('A28:L39')->applyFromArray($styleArray);
        if ($sjjylist) {
            $baseRow2 = 31; //数据从N-1行开始往下输出
            foreach ($sjjylist as $r => $h) {
                $i = $baseRow2 + $r;
                $objActSheet->getRowDimension($i)->setRowHeight(25);
                $objActSheet->setCellValue('A' . $i, $h['nums']);
                $objActSheet->setCellValue('B' . $i, $h['cc1']);
                $objActSheet->setCellValue('C' . $i, $h['cc2']);
                $objActSheet->setCellValue('D' . $i, $h['cc3']);
                $objActSheet->setCellValue('E' . $i, $h['cc4']);
                $objActSheet->setCellValue('F' . $i, $h['cc5']);
                $objActSheet->setCellValue('G' . $i, $h['cc6']);
                $objActSheet->setCellValue('H' . $i, $h['cc7']);
                $objActSheet->setCellValue('I' . $i, $h['cc8']);
                $objActSheet->setCellValue('J' . $i, 2.126);
                $objActSheet->setCellValue('K' . $i, $h['cctxt']);
                $objActSheet->setCellValue('L' . $i, '∣vi∣< λ(a，n)*s');

                //数据异常的显示红色
                if($h['cc1']>$h['cctxt'] ){
                    $objActSheet->getStyle('B'. $i)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
                }
                if($h['cc2']>$h['cctxt'] ){
                    $objActSheet->getStyle('C'. $i)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
                }
                if($h['cc3']>$h['cctxt'] ){
                    $objActSheet->getStyle('D'. $i)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
                }if($h['cc4']>$h['cctxt'] ){
                    $objActSheet->getStyle('E'. $i)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
                }
                if($h['cc5']>$h['cctxt'] ){
                    $objActSheet->getStyle('F'. $i)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
                }
                if($h['cc6']>$h['cctxt'] ){
                    $objActSheet->getStyle('G'. $i)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
                }
                if($h['cc7']>$h['cctxt'] ){
                    $objActSheet->getStyle('H'. $i)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
                }
                if($h['cc8']>$h['cctxt'] ){
                    $objActSheet->getStyle('I'. $i)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
                }
            }
        }

        //狄克逊法检验实验室间可疑值
        //合并单元格
        $objActSheet->mergeCells('A41' .':Q41');
        $objActSheet->mergeCells('O42' .':O43');
        $objActSheet->getStyle('A41')->getFont()->setSize(14);
        $objActSheet->getStyle('A41')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('CCCCCC');
        $objActSheet->setCellValue('A41', '狄克逊法检验实验室间可疑值');

        //文本内容自动换行
        $objActSheet->getStyle('O42')->getAlignment()->setWrapText(true);
        $objActSheet->setCellValue('O42', '如果不满足要求，则将不满足判断要求的异常值用红色显示');
        $objActSheet->getStyle('O42')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('FF0000');
        $objActSheet->getStyle('O42')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLACK);//将A1单元格文字颜色设为红色
        $objActSheet->getStyle('O42')->applyFromArray($styleArray2);


        $objActSheet->setCellValue('A42', '平均值从小到大排列');
        $objActSheet->setCellValue('B42', '¯x(1)');
        $objActSheet->setCellValue('C42', '¯x(2)');
        $objActSheet->setCellValue('D42', '¯x(3)');
        $objActSheet->setCellValue('E42', '¯x(4)');
        $objActSheet->setCellValue('F42', '¯x(5)');
        $objActSheet->setCellValue('G42', '¯x(6)');
        $objActSheet->setCellValue('H42', '¯x(7)');
        $objActSheet->setCellValue('I42', '¯x(8)');
        $objActSheet->setCellValue('J42', '¯x(9)');
        $objActSheet->setCellValue('K42', 'r1');
        $objActSheet->setCellValue('L42', 'rn');
        $objActSheet->setCellValue('M42', 'f(0.05,9)');
        $objActSheet->setCellValue('N42', '判断');
        $objActSheet->setCellValue('P42', 'mean');
        $objActSheet->setCellValue('Q42', 'SD');

        $objActSheet->getStyle('A41'  . ':Q43')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
        $objActSheet->getStyle('A41:Q43')->applyFromArray($styleArray);

        $objActSheet->setCellValue('A43', '');
        $objActSheet->setCellValue('B43', $kyz['kyz1']);
        $objActSheet->setCellValue('C43', $kyz['kyz2']);
        $objActSheet->setCellValue('D43', $kyz['kyz3']);
        $objActSheet->setCellValue('E43', $kyz['kyz4']);
        $objActSheet->setCellValue('F43', $kyz['kyz5']);
        $objActSheet->setCellValue('G43', $kyz['kyz6']);
        $objActSheet->setCellValue('H43', $kyz['kyz7']);
        $objActSheet->setCellValue('I43', $kyz['kyz8']);
        $objActSheet->setCellValue('J43', $kyz['kyz9']);
        $objActSheet->setCellValue('K43', $kyz['r1']);
        $objActSheet->setCellValue('L43', $kyz['rn']);
        $objActSheet->setCellValue('M43', '0.564');
        $objActSheet->setCellValue('N43', 'r1< f(5%,9)rn< f(5%,9)无异常值');
        //$objActSheet->setCellValue('O43', '如果不满足要求，则将不满足判断要求的异常值用红色显示');
        $objActSheet->setCellValue('P43', $kyz['mean']);
        $objActSheet->setCellValue('Q43', $kyz['sd']);
        //如果有异常则加红
        if($kyz['r1']>0.564 && $kyz['rn'] >0.564){
            $objActSheet->getStyle('K43')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
            $objActSheet->getStyle('L43')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
        }
        $objActSheet->getRowDimension(43)->setRowHeight(50);

        //格拉布斯法检验实验室内异常值
       // {$ycz['ycz1']}
        //合并单元格
        $objActSheet->mergeCells('A45' .':M45');
        $objActSheet->getStyle('A45')->getFont()->setSize(14);
        $objActSheet->getStyle('A45')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('CCCCCC');
        $objActSheet->setCellValue('A45', '格拉布斯法检验实验室内异常值 检验9家实验室间数据');
        $objActSheet->mergeCells('A46' .':I46');
        $objActSheet->setCellValue('A46', '测量值残差vi');
        $objActSheet->mergeCells('J46' .':J47');
        $objActSheet->mergeCells('K46' .':K47');
        $objActSheet->mergeCells('L46' .':L47');
        $objActSheet->mergeCells('M46' .':M48');

        $objActSheet->setCellValue('J46', 'λ(0.05, 9)');
        $objActSheet->setCellValue('K46', 'λ(a,n)*s');
        $objActSheet->setCellValue('L46', '判断');

        $objActSheet->getStyle('M46')->getAlignment()->setWrapText(true);
        $objActSheet->setCellValue('M46', '如果不满足要求，则将不满足判断要求的异常值用红色显示');
        $objActSheet->getStyle('M46')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('FF0000');
        $objActSheet->getStyle('M46')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLACK);//将A1单元格文字颜色设为红色
        $objActSheet->getStyle('M46')->applyFromArray($styleArray2);


        $objActSheet->setCellValue('A47', '1');
        $objActSheet->setCellValue('B47', '2');
        $objActSheet->setCellValue('C47', '3');
        $objActSheet->setCellValue('D47', '4');
        $objActSheet->setCellValue('E47', '5');
        $objActSheet->setCellValue('F47', '6');
        $objActSheet->setCellValue('G47', '7');
        $objActSheet->setCellValue('H47', '8');
        $objActSheet->setCellValue('I47', '9');

        $objActSheet->getStyle('A45'  . ':M48')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
        $objActSheet->getStyle('A45:M48')->applyFromArray($styleArray);

        $objActSheet->setCellValue('A48', $ycz['ycz1']);
        $objActSheet->setCellValue('B48', $ycz['ycz2']);
        $objActSheet->setCellValue('C48', $ycz['ycz3']);
        $objActSheet->setCellValue('D48', $ycz['ycz4']);
        $objActSheet->setCellValue('E48', $ycz['ycz5']);
        $objActSheet->setCellValue('F48', $ycz['ycz6']);
        $objActSheet->setCellValue('G48', $ycz['ycz7']);
        $objActSheet->setCellValue('H48', $ycz['ycz8']);
        $objActSheet->setCellValue('I48', $ycz['ycz9']);
        $objActSheet->setCellValue('J48', $ycz['ycz10']);
        $objActSheet->setCellValue('K48', $ycz['ycz11']);
        $objActSheet->setCellValue('L48', '∣vi∣< λ(a n)*s');

        if($ycz['ycz1']>$ycz['ycz11'] ){
            $objActSheet->getStyle('A48')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
        }
        if($ycz['ycz2']>$ycz['ycz11'] ){
            $objActSheet->getStyle('B8')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
        }
        if($ycz['ycz3']>$ycz['ycz11'] ){
            $objActSheet->getStyle('C48')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
        }
        if($ycz['ycz4']>$ycz['ycz11'] ){
            $objActSheet->getStyle('D48')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
        }
        if($ycz['ycz5']>$ycz['ycz11'] ){
            $objActSheet->getStyle('E48')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
        }
        if($ycz['ycz6']>$ycz['ycz11'] ){
            $objActSheet->getStyle('F48')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
        }
        if($ycz['ycz7']>$ycz['ycz11'] ){
            $objActSheet->getStyle('G48')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
        }
        if($ycz['ycz8']>$ycz['ycz11'] ){
            $objActSheet->getStyle('H48')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
        }
        if($ycz['ycz9']>$ycz['ycz11'] ){
            $objActSheet->getStyle('I48')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
        }
        //科克伦法实验室间数据组等精度检验
        //{$jdjy['sz1']}
        //合并单元格
        $objActSheet->mergeCells('A50' .':O50');
        $objActSheet->getStyle('A50')->getFont()->setSize(14);
        $objActSheet->getStyle('A50')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('CCCCCC');
        $objActSheet->setCellValue('A50', '科克伦法实验室间数据组等精度检验');
        $objActSheet->mergeCells('A51' .':I51');
        $objActSheet->setCellValue('A51', '实验室方差');

        $objActSheet->mergeCells('J51' .':J52');
        $objActSheet->mergeCells('K51' .':K52');
        $objActSheet->mergeCells('L51' .':L52');
        $objActSheet->mergeCells('M51' .':M52');
        $objActSheet->mergeCells('N51' .':N52');
        $objActSheet->mergeCells('O51' .':O53');
        //文本内容自动换行
        $objActSheet->getStyle('O51')->getAlignment()->setWrapText(true);
        $objActSheet->setCellValue('O51', '如果不满足要求，则将不满足判断要求的异常值用红色显示');
        $objActSheet->getStyle('O51')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('FF0000');
        $objActSheet->getStyle('O51')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLACK);//将A1单元格文字颜色设为红色
        $objActSheet->getStyle('O51')->applyFromArray($styleArray2);


        $objActSheet->setCellValue('J51', 's2max');
        $objActSheet->setCellValue('K51', '实验室方差和');
        $objActSheet->setCellValue('L51', '科克伦统计量C');
        $objActSheet->setCellValue('M51', 'C(0.05，9，8)');
        $objActSheet->setCellValue('N51', '判断');

        $objActSheet->setCellValue('A52', 'S平方(1)');
        $objActSheet->setCellValue('B52', 'S平方(2)');
        $objActSheet->setCellValue('C52', 'S平方(3)');
        $objActSheet->setCellValue('D52', 'S平方(4)');
        $objActSheet->setCellValue('E52', 'S平方(5)');
        $objActSheet->setCellValue('F52', 'S平方(6)');
        $objActSheet->setCellValue('G52', 'S平方(7)');
        $objActSheet->setCellValue('H52', 'S平方(8)');
        $objActSheet->setCellValue('I52', 'S平方(9)');

        $objActSheet->getStyle('A50'  . ':O53')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
        $objActSheet->getStyle('A50:O53')->applyFromArray($styleArray);


        $objActSheet->setCellValue('A53', $jdjy['sz1']);
        $objActSheet->setCellValue('B53', $jdjy['sz2']);
        $objActSheet->setCellValue('C53', $jdjy['sz3']);
        $objActSheet->setCellValue('D53', $jdjy['sz4']);
        $objActSheet->setCellValue('E53', $jdjy['sz5']);
        $objActSheet->setCellValue('F53', $jdjy['sz6']);
        $objActSheet->setCellValue('G53', $jdjy['sz7']);
        $objActSheet->setCellValue('H53', $jdjy['sz8']);
        $objActSheet->setCellValue('I53', $jdjy['sz9']);
        $objActSheet->setCellValue('J53', $jdjy['max']);
        $objActSheet->setCellValue('K53', $jdjy['sum']);
        $objActSheet->setCellValue('L53', $jdjy['c']);
        $objActSheet->setCellValue('M53', '0.2768');
        $objActSheet->setCellValue('N53', 'C< C(0.05，9，8)实验室间数据平均值等精度');
        $objActSheet->setCellValue('O53', '');

        if(floatval($jdjy['c'])>0.2768){
            $objActSheet->getStyle('L53')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
        }

        $objActSheet->getRowDimension(53)->setRowHeight(80);
        //一致性检验 t-检验: 双样本等方差假设

        //合并单元格
        $objActSheet->mergeCells('A55' .':E55');
        $objActSheet->getStyle('A55')->getFont()->setSize(14);
        $objActSheet->getStyle('A55')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('CCCCCC');
        $objActSheet->setCellValue('A55', '一致性检验 t-检验: 双样本等方差假设');

        $objActSheet->setCellValue('A56', '');
        $objActSheet->setCellValue('B56', '平均值');
        $objActSheet->setCellValue('C56', 'SD');
        $objActSheet->setCellValue('D56', '平均值一致性检验-t检验法');
        $objActSheet->setCellValue('E56', '临界值');
        $objActSheet->getStyle('A55'  . ':E59')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
        $objActSheet->getStyle('A55:E59')->applyFromArray($styleArray);

        $objActSheet->setCellValue('A57', '最大值');
        $objActSheet->setCellValue('B57', $yzx['max']);
        //$objActSheet->setCellValue('B57', '=SUM(D2:D10)');
        $objActSheet->setCellValue('C57', $yzx['max_sds']);
        $objActSheet->setCellValue('D57', '');
        $objActSheet->setCellValue('E57', '');

        $objActSheet->setCellValue('A58', '最小值');
        $objActSheet->setCellValue('B58', $yzx['min']);
        $objActSheet->setCellValue('C58', $yzx['min_sds']);
        $objActSheet->setCellValue('D58', '');
        $objActSheet->setCellValue('E58', '');

        $objActSheet->setCellValue('A59', '');
        $objActSheet->setCellValue('B59', '实验数');
        $objActSheet->setCellValue('C59', $yzx['count']);

        $objActSheet->setCellValue('D59', $yzx['yzxss']);
        $objActSheet->setCellValue('E59', $yzx['ljz']);

        //偏态系数和峰态系数法
        //合并单元格
        $objActSheet->mergeCells('A62' .':J62');
        $objActSheet->getStyle('A62')->getFont()->setSize(14);
        $objActSheet->getStyle('A62')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('CCCCCC');
        $objActSheet->setCellValue('A62', '偏态系数和峰态系数法 正态分布检验');
        //
        $objActSheet->setCellValue('A63', '残差');
        $objActSheet->setCellValue('B63', '实验室');
        $objActSheet->setCellValue('C63', '1');
        $objActSheet->setCellValue('D63', '2');
        $objActSheet->setCellValue('E63', '3');
        $objActSheet->setCellValue('F63', '4');
        $objActSheet->setCellValue('G63', '5');
        $objActSheet->setCellValue('H63', '6');
        $objActSheet->setCellValue('I63', '7');
        $objActSheet->setCellValue('J63', '8');

        $objActSheet->getStyle('A62'  . ':J73')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
        $objActSheet->getStyle('A62:J73')->applyFromArray($styleArray);

        $fb_array=[];
        if ($sjjylist) {
            $baseRow2 = 64; //数据从N-1行开始往下输出
            foreach ($sjjylist as $key => $h) {
                $i = $baseRow2 + $key;
                $objActSheet->getRowDimension($i)->setRowHeight(25);

                $jy1=$h['data1']-$avg;
                $jy2=$h['data2']-$avg;
                $jy3=$h['data3']-$avg;
                $jy4=$h['data4']-$avg;
                $jy5=$h['data5']-$avg;
                $jy6=$h['data6']-$avg;
                $jy7=$h['data7']-$avg;
                $jy8=$h['data8']-$avg;
                $fb_array[]=$jy1;
                $fb_array[]=$jy2;
                $fb_array[]=$jy3;
                $fb_array[]=$jy4;
                $fb_array[]=$jy5;
                $fb_array[]=$jy6;
                $fb_array[]=$jy7;
                $fb_array[]=$jy8;
                //
                $lf1=pow($jy1,3);
                $lf2=pow($jy2,3);
                $lf3=pow($jy3,3);
                $lf4=pow($jy4,3);
                $lf5=pow($jy5,3);
                $lf6=pow($jy6,3);
                $lf7=pow($jy7,3);
                $lf8=pow($jy8,3);
                $sjjylist[$key]['lf1']=sprint_data($lf1,5);
                $sjjylist[$key]['lf2']=sprint_data($lf2,5);
                $sjjylist[$key]['lf3']=sprint_data($lf3,5);
                $sjjylist[$key]['lf4']=sprint_data($lf4,5);
                $sjjylist[$key]['lf5']=sprint_data($lf5,5);
                $sjjylist[$key]['lf6']=sprint_data($lf6,5);
                $sjjylist[$key]['lf7']=sprint_data($lf7,5);
                $sjjylist[$key]['lf8']=sprint_data($lf8,5);
                //
                $jy11=sprint_data($jy1,4);
                $jy21=sprint_data($jy2,4);
                $jy31=sprint_data($jy3,4);
                $jy41=sprint_data($jy4,4);
                $jy51=sprint_data($jy5,4);
                $jy61=sprint_data($jy6,4);
                $jy71=sprint_data($jy7,4);
                $jy81=sprint_data($jy8,4);

                //
                $lb1=pow($jy1,4);
                $lb2=pow($jy2,4);
                $lb3=pow($jy3,4);
                $lb4=pow($jy4,4);
                $lb5=pow($jy5,4);
                $lb6=pow($jy6,4);
                $lb7=pow($jy7,4);
                $lb8=pow($jy8,4);
                $sjjylist[$key]['lb1']=sprint_data($lb1,5);
                $sjjylist[$key]['lb2']=sprint_data($lb2,5);
                $sjjylist[$key]['lb3']=sprint_data($lb3,5);
                $sjjylist[$key]['lb4']=sprint_data($lb4,5);
                $sjjylist[$key]['lb5']=sprint_data($lb5,5);
                $sjjylist[$key]['lb6']=sprint_data($lb6,5);
                $sjjylist[$key]['lb7']=sprint_data($lb7,5);
                $sjjylist[$key]['lb8']=sprint_data($lb8,5);

                $objActSheet->setCellValue('A'.$i, '');
                $objActSheet->setCellValue('B'.$i, $h['nums']);
                $objActSheet->setCellValue('C'.$i, $jy11);
                $objActSheet->setCellValue('D'.$i, $jy21);
                $objActSheet->setCellValue('E'.$i, $jy31);
                $objActSheet->setCellValue('F'.$i, $jy41);
                $objActSheet->setCellValue('G'.$i, $jy51);
                $objActSheet->setCellValue('H'.$i, $jy61);
                $objActSheet->setCellValue('I'.$i, $jy71);
                $objActSheet->setCellValue('J'.$i, $jy81);
            }
        }

        $sum_fb=sum_pingfang($fb_array);
        $count_fb=count($fb_array);
        $fb_m2=sprint_data($sum_fb/$count_fb,4);
        $objActSheet->getRowDimension(73)->setRowHeight(25);
        $objActSheet->setCellValue('A73', '');
        $objActSheet->setCellValue('B73', 'm2');
        $objActSheet->setCellValue('C73', $fb_m2);
        $objActSheet->setCellValue('D73', '');
        $objActSheet->setCellValue('E73', '');
        $objActSheet->setCellValue('F73', '');
        $objActSheet->setCellValue('G73', '');
        $objActSheet->setCellValue('H73', '');
        $objActSheet->setCellValue('I73', '');
        $objActSheet->setCellValue('J73', '');

        //
        //75
        $objActSheet->setCellValue('A75', '');
        $objActSheet->setCellValue('B75', 'Lab');
        $objActSheet->setCellValue('C75', '1');
        $objActSheet->setCellValue('D75', '2');
        $objActSheet->setCellValue('E75', '3');
        $objActSheet->setCellValue('F75', '4');
        $objActSheet->setCellValue('G75', '5');
        $objActSheet->setCellValue('H75', '6');
        $objActSheet->setCellValue('I75', '7');
        $objActSheet->setCellValue('J75', '8');

        $objActSheet->getRowDimension(75)->setRowHeight(25);

        $objActSheet->getStyle('A75'  . ':J85')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
        $objActSheet->getStyle('A75:J85')->applyFromArray($styleArray);

        $fb2_array=[];
        if ($sjjylist) {
            $baseRow3 = 76; //数据从N-1行开始往下输出
            foreach ($sjjylist as $key => $h) {
                $i = $baseRow3 + $key;
                $fb2_array[]=$h['lf1'];
                $fb2_array[]=$h['lf2'];
                $fb2_array[]=$h['lf3'];
                $fb2_array[]=$h['lf4'];
                $fb2_array[]=$h['lf5'];
                $fb2_array[]=$h['lf6'];
                $fb2_array[]=$h['lf7'];
                $fb2_array[]=$h['lf8'];
                $objActSheet->getRowDimension($i)->setRowHeight(25);
                Log::info('lf1='.$h['lf1']);
                $objActSheet->setCellValue('A'.$i, '');
                $objActSheet->setCellValue('B'.$i, $h['nums']);
                $objActSheet->setCellValue('C'.$i, "'".$h['lf1']);
                $objActSheet->setCellValue('D'.$i, "'".$h['lf2']);
                $objActSheet->setCellValue('E'.$i, "'".$h['lf3']);
                $objActSheet->setCellValue('F'.$i, "'".$h['lf4']);
                $objActSheet->setCellValue('G'.$i, "'".$h['lf5']);
                $objActSheet->setCellValue('H'.$i, "'".$h['lf6']);
                $objActSheet->setCellValue('I'.$i, "'".$h['lf7']);
                $objActSheet->setCellValue('J'.$i, "'".$h['lf8']);
            }
        }
        //=SUM(C73:J81)/COUNT(C73:J81)
        $sum_m3=sprint_data(sum_array($fb2_array)/count($fb2_array),9);

        $objActSheet->getRowDimension(85)->setRowHeight(25);
        $objActSheet->setCellValue('A85', '');
        $objActSheet->setCellValue('B85', 'm3');
        $objActSheet->setCellValue('C85', $sum_m3);
        $objActSheet->setCellValue('D85', '');
        $objActSheet->setCellValue('E85', '');
        $objActSheet->setCellValue('F85', '');
        $objActSheet->setCellValue('G85', '');
        $objActSheet->setCellValue('H85', '');
        $objActSheet->setCellValue('I85', '');
        $objActSheet->setCellValue('J85', '');

        //87
        $objActSheet->setCellValue('A87', '');
        $objActSheet->setCellValue('B87', 'Lab');
        $objActSheet->setCellValue('C87', '1');
        $objActSheet->setCellValue('D87', '2');
        $objActSheet->setCellValue('E87', '3');
        $objActSheet->setCellValue('F87', '4');
        $objActSheet->setCellValue('G87', '5');
        $objActSheet->setCellValue('H87', '6');
        $objActSheet->setCellValue('I87', '7');
        $objActSheet->setCellValue('J87', '8');

        $objActSheet->getRowDimension(87)->setRowHeight(25);

        $objActSheet->getStyle('A87'.':J97')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
        $objActSheet->getStyle('A87:J97')->applyFromArray($styleArray);

        $lb1_array=[];
        if ($sjjylist) {
            $baseRow4 = 88; //数据从N-1行开始往下输出
            foreach ($sjjylist as $key => $h) {
                $i = $baseRow4 + $key;
                $lb1_array[]=$h['lb1'];
                $lb1_array[]=$h['lb2'];
                $lb1_array[]=$h['lb3'];
                $lb1_array[]=$h['lb4'];
                $lb1_array[]=$h['lb5'];
                $lb1_array[]=$h['lb6'];
                $lb1_array[]=$h['lb7'];
                $lb1_array[]=$h['lb8'];
                $objActSheet->getRowDimension($i)->setRowHeight(25);
                $objActSheet->setCellValue('A'.$i, '');
                $objActSheet->setCellValue('B'.$i, $h['nums']);
                $objActSheet->setCellValue('C'.$i, "'".$h['lb1']);
                $objActSheet->setCellValue('D'.$i, "'".$h['lb2']);
                $objActSheet->setCellValue('E'.$i, "'".$h['lb3']);
                $objActSheet->setCellValue('F'.$i, "'".$h['lb4']);
                $objActSheet->setCellValue('G'.$i, "'".$h['lb5']);
                $objActSheet->setCellValue('H'.$i, "'".$h['lb6']);
                $objActSheet->setCellValue('I'.$i, "'".$h['lb7']);
                $objActSheet->setCellValue('J'.$i, "'".$h['lb8']);

             }
        }
        //=SUM(C85:J93)/COUNT(C85:J93)
        $sum_m4=sum_array($lb1_array)/count($lb1_array);
        $objActSheet->getRowDimension(97)->setRowHeight(25);
        $objActSheet->setCellValue('A97', '');
        $objActSheet->setCellValue('B97', 'm4');
        $objActSheet->setCellValue('C97', $sum_m4);
        $objActSheet->setCellValue('D97', '');
        $objActSheet->setCellValue('E97', '');
        $objActSheet->setCellValue('F97', '');
        $objActSheet->setCellValue('G97', '');
        $objActSheet->setCellValue('H97', '');
        $objActSheet->setCellValue('I97', '');
        $objActSheet->setCellValue('J97', '');

        //
        $objActSheet->getStyle('A99'.':E101')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
        $objActSheet->getStyle('A99:E101')->applyFromArray($styleArray);

        $objActSheet->getRowDimension(99)->setRowHeight(25);
        $objActSheet->setCellValue('A99', '');
        $objActSheet->setCellValue('B99', '');
        $objActSheet->setCellValue('C99', '临界值（0.95,72）');
        $objActSheet->setCellValue('D99', '判断');
        $objActSheet->setCellValue('E99', '结论');

        //=ABS(C82)/SQRT(POWER(C70,3))
        $ptxsa=sprint_data(abs(floatval($sum_m3))/sqrt(pow(floatval($fb_m2),3)),4);
        if($ptxsa<0.46){
            $psxzt='正态分布';
        }else{
            $psxzt='有异常';
        }
        //=C94/POWER(C70,2)
        $ftxsb=sprint_data($sum_m4/pow($fb_m2,2),4);
        if($ftxsb>2.15 && $ftxsb<3.99){
            $ftxzt='正态分布';
        }else{
            $ftxzt='有异常';
        }

        $objActSheet->getRowDimension(100)->setRowHeight(25);
        $objActSheet->setCellValue('A100', '偏态系数A');
        $objActSheet->setCellValue('B100', $ptxsa);
        $objActSheet->setCellValue('C100', '0.46');
        $objActSheet->setCellValue('D100', 'A<A1');
        $objActSheet->setCellValue('E100', $psxzt);

        $objActSheet->getRowDimension(101)->setRowHeight(25);
        $objActSheet->setCellValue('A101', '峰态系数B');
        $objActSheet->setCellValue('B101', $ftxsb);
        $objActSheet->setCellValue('C101', '2.15-3.99');
        $objActSheet->setCellValue('D101', 'B1<B<B1');
        $objActSheet->setCellValue('E101', $ftxzt);


        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="数据检验9家（'. date('Y-m-d')  .'）'.'.xlsx"');
        header('Cache-Control: max-age=0');
        $objWriter->save('php://output');

    }

    //开始计算均匀性10家
    public function start_sjjy_ten(){
        $where=[];
        $where[]=['uid','=',get_login_admin('id')];
        $where[]=['type','=',2];
        //Log::info('start_sjjy');
        $sjjylist=sjjylist::where($where)->order('id asc')->select();
        $i=0;
        $sum_avgs=0;
        $avg_arr=[];

        foreach($sjjylist as $key=>$v){
            $i++;
            //$rsd=round(floatval($v['rsd']),3);
            $sds=floatval($v['sds']);
            $avgs11=floatval($v['avgs']);
            $sum_avgs+=$avgs11;
            $avg_arr[]=floatval($avgs11);
            //=SMALL(C3:J3,1)
            $data_arr=[floatval($v['data1']),floatval($v['data2']),floatval($v['data3']),floatval($v['data4']),floatval($v['data5']),floatval($v['data6']),floatval($v['data7']),floatval($v['data8'])];
            //Log::info('data_arr='.json_encode($data_arr));
            $x1=Statistical::SMALL($data_arr,1);
            $x2=Statistical::SMALL($data_arr,2);
            $x3=Statistical::SMALL($data_arr,3);
            $x4=Statistical::SMALL($data_arr,4);
            $x5=Statistical::SMALL($data_arr,5);
            $x6=Statistical::SMALL($data_arr,6);
            $x7=Statistical::SMALL($data_arr,7);
            $x8=Statistical::SMALL($data_arr,8);
            $sjjylist[$key]['x1']=sprint_data($x1,2);
            $sjjylist[$key]['x2']=sprint_data($x2,2);
            $sjjylist[$key]['x3']=sprint_data($x3,2);
            $sjjylist[$key]['x4']=sprint_data($x4,2);
            $sjjylist[$key]['x5']=sprint_data($x5,2);
            $sjjylist[$key]['x6']=sprint_data($x6,2);
            $sjjylist[$key]['x7']=sprint_data($x7,2);
            $sjjylist[$key]['x8']=sprint_data($x8,2);
            //=(D17-C17)/(I17-C17)
            $r1=($x2-$x1)/($x7-$x1);
            $sjjylist[$key]['r1']=sprint_data($r1,3);
            //=(J17-I17)/(J17-D17)
            $r2=($x8-$x7)/($x8-$x2);
            $sjjylist[$key]['r2']=sprint_data($r2,3);
            //r1< f(5%,8)
            //且
            //rn< f(5%,8)
            //无异常值
            if($r1<0.608 && $r2<0.608){
                $zt='正常';
            }else{
                $zt='<font color=red>有异常值</font>';
            }
            $sjjylist[$key]['zt']=$zt;
            //=C17-$N$3
            //第2个表的数值
            $cc1=sprint_data($x1-$avgs11,2);
            $cc2=sprint_data($x2-$avgs11,2);
            $cc3=sprint_data($x3-$avgs11,2);
            $cc4=sprint_data($x4-$avgs11,2);
            $cc5=sprint_data($x5-$avgs11,2);
            $cc6=sprint_data($x6-$avgs11,2);
            $cc7=sprint_data($x7-$avgs11,2);
            $cc8=sprint_data($x8-$avgs11,2);
            $sjjylist[$key]['cc1']=$cc1;
            $sjjylist[$key]['cc2']=$cc2;
            $sjjylist[$key]['cc3']=$cc3;
            $sjjylist[$key]['cc4']=$cc4;
            $sjjylist[$key]['cc5']=$cc5;
            $sjjylist[$key]['cc6']=$cc6;
            $sjjylist[$key]['cc7']=$cc7;
            $sjjylist[$key]['cc8']=$cc8;
            //=K30*O3
            $cctxt=sprint_data(2.126*$sds,2);
            //$cctxt=0.01;
            $sjjylist[$key]['cctxt']=$cctxt;

        }
        $nums=10;
        $avg=$sum_avgs/$nums;
        //Log::info('range33='.json_encode($avg_arr));
        $sd = sprint_data(Statistical::STDEV($avg_arr),4);
        //=L3/K3
        $rsd=sprint_data($sd/$avg*100,4);
        //狄克逊法检验实验室间可疑值
        //可疑值
        //=SMALL($N$3:$N$11,1)
        $kyz['kyz1']=Statistical::SMALL($avg_arr,1);
        $kyz['kyz2']=Statistical::SMALL($avg_arr,2);
        $kyz['kyz3']=Statistical::SMALL($avg_arr,3);
        $kyz['kyz4']=Statistical::SMALL($avg_arr,4);
        $kyz['kyz5']=Statistical::SMALL($avg_arr,5);
        $kyz['kyz6']=Statistical::SMALL($avg_arr,6);
        $kyz['kyz7']=Statistical::SMALL($avg_arr,7);
        $kyz['kyz8']=Statistical::SMALL($avg_arr,8);
        $kyz['kyz9']=Statistical::SMALL($avg_arr,9);
        $kyz['kyz10']=Statistical::SMALL($avg_arr,10);
        //r1 =(D44-C44)/(K44-C44)
        $kyz['r1']=sprint_data(($kyz['kyz2']-$kyz['kyz1'])/($kyz['kyz9']-$kyz['kyz1']),3);
        //rn =(L44-K44)/(L44-D44)
        $kyz['rn']=sprint_data(($kyz['kyz10']-$kyz['kyz9'])/($kyz['kyz10']-$kyz['kyz2']),3);
        /*r1< f(5%,9)且rn< f(5%,9)无异常值*/
        if($kyz['r1']<0.53 && $kyz['rn']<0.53){
            $kyz['zt']='无异常';
        }else{
            $kyz['zt']='<font color=red>有异常</font>';
        }
        //mean =AVERAGE(C44:L44)
        $kzy_range=[$kyz['kyz1'],$kyz['kyz2'],$kyz['kyz3'],$kyz['kyz4'],$kyz['kyz5'],$kyz['kyz6'],$kyz['kyz7'],$kyz['kyz8'],$kyz['kyz9'],$kyz['kyz10']];
        $kyz['mean']=sprint_data(Statistical::AVERAGE($kzy_range),3);
        //SD =STDEV(C44:L44)
        $kyz['sd']=sprint_data(Statistical::STDEV($kzy_range),8);
        //格拉布斯法检验实验室内异常值
        //=C42-$Q$42
        $ycz['ycz1']=sprint_data($kyz['kyz1']-$kyz['mean'],3);
        $ycz['ycz2']=sprint_data($kyz['kyz2']-$kyz['mean'],3);
        $ycz['ycz3']=sprint_data($kyz['kyz3']-$kyz['mean'],3);
        $ycz['ycz4']=sprint_data($kyz['kyz4']-$kyz['mean'],3);
        $ycz['ycz5']=sprint_data($kyz['kyz5']-$kyz['mean'],3);
        $ycz['ycz6']=sprint_data($kyz['kyz6']-$kyz['mean'],3);
        $ycz['ycz7']=sprint_data($kyz['kyz7']-$kyz['mean'],3);
        $ycz['ycz8']=sprint_data($kyz['kyz8']-$kyz['mean'],3);
        $ycz['ycz9']=sprint_data($kyz['kyz9']-$kyz['mean'],3);
        $ycz['ycz10']=sprint_data($kyz['kyz10']-$kyz['mean'],3);
        //λ(0.05, 10) 2.29
        $ycz11=2.290;
        $ycz['ycz11']=$ycz11;
        //λ(a,n)*s =M48*S44
        $ycz12=sprint_data($ycz11*$kyz['sd'],4);
        $ycz['ycz12']=$ycz12;
        //科克伦法实验室间数据组等精度检验
        //=O3^2
        $jdjy_array=sjjylist::where($where)->order('id asc')->column('sds');
        //$sds_max=sjjylist::where($where)->order('id asc')->max('sds');
        //$sds_min=sjjylist::where($where)->order('id asc')->min('sds');
        $jdjy['sz1']=sprint_data(pow($jdjy_array[0],2),4);
        $jdjy['sz2']=sprint_data(pow($jdjy_array[1],2),4);
        $jdjy['sz3']=sprint_data(pow($jdjy_array[2],2),4);
        $jdjy['sz4']=sprint_data(pow($jdjy_array[3],2),4);
        $jdjy['sz5']=sprint_data(pow($jdjy_array[4],2),4);
        $jdjy['sz6']=sprint_data(pow($jdjy_array[5],2),4);
        $jdjy['sz7']=sprint_data(pow($jdjy_array[6],2),4);
        $jdjy['sz8']=sprint_data(pow($jdjy_array[7],2),4);
        $jdjy['sz9']=sprint_data(pow($jdjy_array[8],2),4);
        $jdjy['sz10']=sprint_data(pow($jdjy_array[9],2),4);
        //=MAX(C50:K50)
        //Log::info('jdjy_array='.json_encode($jdjy_array));
        $jdjy_array2=[floatval($jdjy['sz1']),floatval($jdjy['sz2']),floatval($jdjy['sz3']),floatval($jdjy['sz4']),floatval($jdjy['sz5']),floatval($jdjy['sz6']),floatval($jdjy['sz7']),floatval($jdjy['sz8']),floatval($jdjy['sz9']),floatval($jdjy['sz10'])];
        $jdjy['max']=Statistical::MAX($jdjy_array2);//最大值
        $jdjy['sum']=sprint_data(getSum($jdjy_array2),4);//求和
        //=L50/M50 科克伦统计量C
        if($jdjy['sum']>0){
            $jdjy['c']=sprint_data($jdjy['max']/$jdjy['sum'],4);
        }else{
            $jdjy['c']=0;
        }
        //C(0.05，9，8) 0.2768
        $jdjy['c2']=0.2768;
        //C< C(0.05，9，8)
        //实验室间数据平均值等精度
        if($jdjy['c']<$jdjy['c2']){
            $jdjy['zt']='正常';
        }else{
            $jdjy['zt']='不满足';
        }
        //一致性检验
        //=MAX(N3:N11)
        //=MIN(N3:N11)
        $yzx['max']=sprint_data(Statistical::MAX($avg_arr),2);
        $yzx['min']=sprint_data(Statistical::MIN($avg_arr),2);
        //=O3
        $yzx['max_sds']=sprint_data(floatval($jdjy_array[0]),4);
        //=O7
        $yzx['min_sds']=sprint_data(floatval($jdjy_array[4]),4);
        //=COUNT(C3:J3)
        $yzx['count']=8;
        //=(D55-D56)/(((((E55^2)*(E57-1))+((E56^2)*(E57-1)))/((E57*2-2)))*((E57+E57)/(E57*E57)))^0.5
        $ttt1=pow($yzx['max_sds'],2);
        $ttt2=pow($yzx['min_sds'],2);
        $yzxss2=((((($ttt1)*($yzx['count']-1))+(($ttt2)*($yzx['count']-1)))/(($yzx['count']*2-2)))*(($yzx['count']+$yzx['count'])/($yzx['count']*$yzx['count'])));
        $yzxss21=pow($yzxss2,0.5);
        $yzx['yzxss']=($yzx['max']-$yzx['min'])/$yzxss21;
        //临界值 =TINV(0.05,E55*2-2)
        $ljz=Statistical::TINV(0.05,$yzx['count']*2-2);
        $yzx['ljz']=sprint_data($ljz,9);

        $html = '<table class="layui-table layui-table-form">';
        $html .= "<tr><td align=\"center\">平均值</td><td>SD</td><td>RSD(%)</td></tr>";

        //
        if ($sjjylist) {
            $html .= "<tr><td align=\"center\">{$avg}</td><td>{$sd}</td><td>{$rsd}%</td></tr>";
        }
        $html.='</table>';
        $html.='<div style="margin-bottom: 20px"></div><br>';
        $html .= '<table class="layui-table layui-table-form">';
        $html .= "<tr><td align=\"center\">平均值</td><td>SD</td><td>RSD(%)</td></tr>";
        if ($sjjylist) {
            foreach ($sjjylist as $h) {
                $avgs=sprint_data(floatval($h['avgs']),3);
                $sds=sprint_data(floatval($h['sds']),3);
                $rsd=sprint_data(floatval($h['rsd']),3);
                $html .= "<tr><td>{$avgs}</td><td>{$sds}</td><td>{$rsd}</td></tr>";
            }
        }
        $html.='</table>';
        $html.='<div style="margin-top: 20px;margin-bottom: 20px"><h4>狄克逊法检验实验室内可疑值 r1< f<sub>(5%,8)</sub> && rn< f<sub>(5%,8)</sub>无异常值</h4></div><br>';
        $html.='<table class="layui-table layui-table-form">';
        $html .= "<tr><td align=\"center\">实验室</td><td>x<sub>(1)</sub></td><td>x<sub>(2)</sub></td><td align=\"center\"><sub>(3)</sub></td><td>x<sub>(4)</sub></td><td>x<sub>(5)</sub></td><td>x<sub>(6)</sub></td><td>x<sub>(7)</sub></td><td>x<sub>(8)</sub></td><td>r<sub>1</sub></td><td>r<sub>n</sub></td><td>f<sub>(0.05,8)</sub></td><td>判断</td></tr>";

        if ($sjjylist) {
            foreach ($sjjylist as $h) {
                $html .= "<tr><td align=\"center\">{$h['nums']}</td><td>{$h['x1']}</td><td>{$h['x2']}</td><td align=\"center\">{$h['x3']}</td><td>{$h['x4']}</td><td>{$h['x5']}</td><td>{$h['x6']}</td><td>{$h['x7']}</td><td>{$h['x8']}</td><td>{$h['r1']}</td><td>{$h['r2']}</td><td>0.608</td><td>{$h['zt']}</td></tr>";
            }
        }
        $html.='</table>';

        $html.='<div style="margin-top: 20px;margin-bottom: 20px"><h4>格拉布斯法检验实验室内异常值(测量值残差vi)(∣vi∣< λ<sub>(a,n)*s</sub>不符合要求的红色标明</h4></div><br>';
        $html.='<table class="layui-table layui-table-form">';
        $html .= "<tr><td align=\"center\">实验室</td><td>1</td><td>2</td><td align=\"center\">3</td><td>4</td><td>5</td><td>6</td><td>7</td><td>8</td><td>λ<sub>(0.05,8)</sub></td><td>λ<sub>(a,n)*s</sub></td><td>判断</td></tr>";

        if ($sjjylist) {
            foreach ($sjjylist as $h) {
                if($h['cc1']>$h['cctxt'] || $h['cc2']>$h['cctxt'] || $h['cc3']>$h['cctxt'] || $h['cc4']>$h['cctxt'] || $h['cc5']>$h['cctxt'] || $h['cc6']>$h['cctxt'] || $h['cc7']>$h['cctxt'] || $h['cc8']>$h['cctxt']){
                    $isyc='<font color=red>有异常</font>';
                }else{
                    $isyc='正常';
                }
                $html .= "<tr><td align=\"center\">{$h['nums']}</td><td>{$h['cc1']}</td><td>{$h['cc2']}</td><td align=\"center\">{$h['cc3']}</td><td>{$h['cc4']}</td><td>{$h['cc5']}</td><td>{$h['cc6']}</td><td>{$h['cc7']}</td><td>{$h['cc8']}</td><td>2.126</td><td>{$h['cctxt']}</td><td>{$isyc}</td></tr>";
            }
        }
        $html.='</table>';

        $html.='<div style="margin-top: 20px;margin-bottom: 20px"><h4>狄克逊法检验实验室间可疑值 (r1< f<sub>(5%,9)</sub>且rn< f<sub>(5%,9)</sub>无异常值)</h4></div><br>';
        $html.='<table class="layui-table layui-table-form">';
        $html .= "<tr><td align=\"center\">¯x<sub>(1)</sub></td><td>¯x<sub>(2)</sub></td><td>¯x<sub>(3)</sub></td><td align=\"center\">¯x<sub>(4)</sub></td><td>¯x<sub>(5)</sub></td><td>¯x<sub>(6)</sub></td><td>¯x<sub>(7)</sub></td><td>¯x<sub>(8)</sub></td><td>¯x<sub>(9)</sub></td><td>¯x<sub>(10)</sub></td><td>r<sub>1</sub></td><td>r<sub>n</sub></td><td>f<sub>(0.05,9)</sub></td><td>判断</td><td>mean</td><td>SD</td></tr>";
        $html .= "<tr><td align=\"center\">{$kyz['kyz1']}</td><td>{$kyz['kyz2']}</td><td>{$kyz['kyz3']}</td><td align=\"center\">{$kyz['kyz4']}</td><td>{$kyz['kyz5']}</td><td>{$kyz['kyz6']}</td><td>{$kyz['kyz7']}</td><td>{$kyz['kyz8']}</td><td>{$kyz['kyz9']}</td><td>{$kyz['kyz10']}</td><td>{$kyz['r1']}</td><td>{$kyz['rn']}</td><td>0.53</td><td>{$kyz['zt']}</td><td>{$kyz['mean']}</td><td>{$kyz['sd']}</td></tr>";
        $html.='</table>';
        //格拉布斯法检验实验室内异常值
        $html.='<div style="margin-top: 20px;margin-bottom: 20px"><h4>格拉布斯法检验实验室内异常值 检验9家实验室间数据 测量值残差vi(∣vi∣< λ<sub>(a n)*s)</sub></h4></div><br>';
        $html.='<table class="layui-table layui-table-form">';
        $html .= "<tr><td align=\"center\">1</td><td>2</td><td>3</td><td align=\"center\">4</td><td>5</td><td>6</td><td>7</td><td>8</td><td>9</td><td>10</td><td>λ<sub>(0.05,10)</sub></td><td>λ<sub>(a,n)*s</sub></td><td>判断</td></tr>";
        $html .= "<tr><td align=\"center\">{$ycz['ycz1']}</td><td>{$ycz['ycz2']}</td><td>{$ycz['ycz3']}</td><td align=\"center\">{$ycz['ycz4']}</td><td>{$ycz['ycz5']}</td><td>{$ycz['ycz6']}</td><td>{$ycz['ycz7']}</td><td>{$ycz['ycz8']}</td><td>{$ycz['ycz9']}</td><td>{$ycz['ycz10']}</td><td>{$ycz['ycz11']}</td><td>{$ycz['ycz12']}</td><td>∣vi∣< λ<sub>(a n)*s</sub></td></tr>";
        $html.='</table>';
        //科克伦法实验室间数据组等精度检验
        $html.='<div style="margin-top: 20px;margin-bottom: 20px"><h4>科克伦法实验室间数据组等精度检验(C< C<sub>(0.05，10，8)</sub>实验室间数据平均值等精度)</h4></div><br>';
        $html.='<table class="layui-table layui-table-form">';
        $html .= "<tr><td align=\"center\">S<sup>2</sup><sub>(1)</sub></td><td>S<sup>2</sup><sub>(2)</sub></td><td>S<sup>2</sup><sub>(3)</sub></td><td>S<sup>2</sup><sub>(4)</sub></td><td>S<sup>2</sup><sub>(5)</sub>)</td><td>S<sup>2</sup><sub>(6)</sub></td><td>S<sup>2</sup><sub>(7)</sub></td><td>S<sup>2</sup><sub>(8)</sub></td><td>S<sup>2</sup><sub>(9)</sub></td><td>S<sup>2</sup><sub>(10)</sub></td><td>s<sup>2</sup><sub>max</sub></td><td>实验室方差和</td><td>科克伦统计量C</td><td>C<sub>(0.05，10，8)</sub></td><td>判断</td></tr>";
        $html .= "<tr><td align=\"center\">{$jdjy['sz1']}</td><td>{$jdjy['sz2']}</td><td>{$jdjy['sz3']}</td><td>{$jdjy['sz4']}</td><td>{$jdjy['sz5']}</td><td>{$jdjy['sz6']}</td><td>{$jdjy['sz7']}</td><td>{$jdjy['sz8']}</td><td>{$jdjy['sz9']}</td><td>{$jdjy['sz10']}</td><td>{$jdjy['max']}</td><td>{$jdjy['sum']}</td><td>{$jdjy['c']}</td><td>{$jdjy['c2']}</td><td>{$jdjy['zt']}</td></tr>";
        $html.='</table>';

        //一致性检验
        $html.='<div style="margin-top: 20px;margin-bottom: 20px"><h4>一致性检验 t-检验: 双样本等方差假设</h4></div><br>';
        $html.='<table class="layui-table layui-table-form">';
        $html .= "<tr><td align=\"center\"></td><td>平均值</td><td>SD</td><td>平均值一致性检验-t检验法</td><td>临界值</td></tr>";
        $html .= "<tr><td align=\"center\">最大值</td><td>{$yzx['max']}</td><td>{$yzx['max_sds']}</td><td></td><td></td></tr>";
        $html .= "<tr><td align=\"center\">最小值</td><td>{$yzx['min']}</td><td>{$yzx['min_sds']}</td><td></td><td></td></tr>";
        $html .= "<tr><td align=\"center\"></td><td>实验数</td><td>{$yzx['count']}</td><td>{$yzx['yzxss']}</td><td>{$yzx['ljz']}</td></tr>";
        $html.='</table>';
        //偏态系数和峰态系数法
        $html.='<div style="margin-top: 20px;margin-bottom: 20px"><h4>偏态系数和峰态系数法 正态分布检验</h4></div><br>';
        $html.='<table class="layui-table layui-table-form">';
        $html .= "<tr><td align=\"center\">残差</td><td>实验室</td><td>1</td><td>2</td><td>3</td><td>4</td><td>5</td><td>6</td><td>7</td><td>8</td></tr>";
        $fb_array=[];
        if ($sjjylist) {
            foreach ($sjjylist as $key=> $h) {
                $jy1=$h['data1']-$avg;
                $jy2=$h['data2']-$avg;
                $jy3=$h['data3']-$avg;
                $jy4=$h['data4']-$avg;
                $jy5=$h['data5']-$avg;
                $jy6=$h['data6']-$avg;
                $jy7=$h['data7']-$avg;
                $jy8=$h['data8']-$avg;
                $fb_array[]=$jy1;
                $fb_array[]=$jy2;
                $fb_array[]=$jy3;
                $fb_array[]=$jy4;
                $fb_array[]=$jy5;
                $fb_array[]=$jy6;
                $fb_array[]=$jy7;
                $fb_array[]=$jy8;
                //
                $lf1=pow($jy1,3);
                $lf2=pow($jy2,3);
                $lf3=pow($jy3,3);
                $lf4=pow($jy4,3);
                $lf5=pow($jy5,3);
                $lf6=pow($jy6,3);
                $lf7=pow($jy7,3);
                $lf8=pow($jy8,3);
                $sjjylist[$key]['lf1']=sprint_data($lf1,5);
                $sjjylist[$key]['lf2']=sprint_data($lf2,5);
                $sjjylist[$key]['lf3']=sprint_data($lf3,5);
                $sjjylist[$key]['lf4']=sprint_data($lf4,5);
                $sjjylist[$key]['lf5']=sprint_data($lf5,5);
                $sjjylist[$key]['lf6']=sprint_data($lf6,5);
                $sjjylist[$key]['lf7']=sprint_data($lf7,5);
                $sjjylist[$key]['lf8']=sprint_data($lf8,5);
                //
                $jy11=sprint_data($jy1,4);
                $jy21=sprint_data($jy2,4);
                $jy31=sprint_data($jy3,4);
                $jy41=sprint_data($jy4,4);
                $jy51=sprint_data($jy5,4);
                $jy61=sprint_data($jy6,4);
                $jy71=sprint_data($jy7,4);
                $jy81=sprint_data($jy8,4);

                //
                $lb1=pow($jy1,4);
                $lb2=pow($jy2,4);
                $lb3=pow($jy3,4);
                $lb4=pow($jy4,4);
                $lb5=pow($jy5,4);
                $lb6=pow($jy6,4);
                $lb7=pow($jy7,4);
                $lb8=pow($jy8,4);
                $sjjylist[$key]['lb1']=sprint_data($lb1,9);
                $sjjylist[$key]['lb2']=sprint_data($lb2,9);
                $sjjylist[$key]['lb3']=sprint_data($lb3,9);
                $sjjylist[$key]['lb4']=sprint_data($lb4,9);
                $sjjylist[$key]['lb5']=sprint_data($lb5,9);
                $sjjylist[$key]['lb6']=sprint_data($lb6,9);
                $sjjylist[$key]['lb7']=sprint_data($lb7,9);
                $sjjylist[$key]['lb8']=sprint_data($lb8,9);

                $html .= "<tr><td align=\"center\"></td><td>{$h['nums']}</td><td>{$jy11}</td><td>{$jy21}</td><td>{$jy31}</td><td>{$jy41}</td><td>{$jy51}</td><td>{$jy61}</td><td>{$jy71}</td><td>{$jy81}</td></tr>";
            }
        }
        //Log::info('fb_array='.json_encode($fb_array));
        $sum_fb=sum_pingfang($fb_array);
        $count_fb=count($fb_array);
        $fb_m2=sprint_data($sum_fb/$count_fb,4);
        //Log::info('sum_fb='.$sum_fb);
        //=SUMSQ(C61:J69)/COUNT(C61:J69)
        $html .= "<tr><td align=\"center\"></td><td>m<sub>2</sub></td><td>{$fb_m2}</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>";
        $html .= "<tr><td align=\"center\"></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>";
        //
        $html .= "<tr><td align=\"center\"></td><td align=\"center\">Lab</td><td>1</td><td>2</td><td>3</td><td>4</td><td>5</td><td>6</td><td>7</td><td>8</td></tr>";
        $fb2_array=[];
        if ($sjjylist) {
            foreach ($sjjylist as $key => $h) {
                $fb2_array[]=$h['lf1'];
                $fb2_array[]=$h['lf2'];
                $fb2_array[]=$h['lf3'];
                $fb2_array[]=$h['lf4'];
                $fb2_array[]=$h['lf5'];
                $fb2_array[]=$h['lf6'];
                $fb2_array[]=$h['lf7'];
                $fb2_array[]=$h['lf8'];
                $html .= "<tr><td align=\"center\"></td><td align=\"center\">{$h['nums']}</td><td>{$h['lf1']}</td><td>{$h['lf2']}</td><td>{$h['lf3']}</td><td>{$h['lf4']}</td><td>{$h['lf5']}</td><td>{$h['lf6']}</td><td>{$h['lf7']}</td><td>{$h['lf8']}</td></tr>";
            }
        }
        //=SUM(C73:J81)/COUNT(C73:J81)
        $sum_m3=sprint_data(sum_array($fb2_array)/count($fb2_array),9);
        $html .= "<tr><td align=\"center\"></td><td>m<sub>3</sub></td><td>{$sum_m3}</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>";
        $html .= "<tr><td align=\"center\"></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>";

        $html .= "<tr><td align=\"center\"></td><td align=\"center\">Lab</td><td>1</td><td>2</td><td>3</td><td>4</td><td>5</td><td>6</td><td>7</td><td>8</td></tr>";

        $lb1_array=[];
        if ($sjjylist) {
            foreach ($sjjylist as $key => $h) {
                $lb1_array[]=$h['lb1'];
                $lb1_array[]=$h['lb2'];
                $lb1_array[]=$h['lb3'];
                $lb1_array[]=$h['lb4'];
                $lb1_array[]=$h['lb5'];
                $lb1_array[]=$h['lb6'];
                $lb1_array[]=$h['lb7'];
                $lb1_array[]=$h['lb8'];
                $html .= "<tr><td align=\"center\"></td><td align=\"center\">{$h['nums']}</td><td>{$h['lb1']}</td><td>{$h['lb2']}</td><td>{$h['lb3']}</td><td>{$h['lb4']}</td><td>{$h['lb5']}</td><td>{$h['lb6']}</td><td>{$h['lb7']}</td><td>{$h['lb8']}</td></tr>";
            }
        }
        //=SUM(C85:J93)/COUNT(C85:J93)
        $sum_m4=sum_array($lb1_array)/count($lb1_array);
        $html .= "<tr><td align=\"center\"></td><td>m<sub>4</sub></td><td>{$sum_m4}</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>";
        $html .= "<tr><td align=\"center\"></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>";

        $html .= "<tr><td align=\"center\"></td><td></td><td></td><td>临界值（0.95,80）</td><td>判断</td><td>结论</td><td></td><td></td><td></td><td></td></tr>";
        //=ABS(C86)/SQRT(POWER(C73,3))
        $ptxsa=sprint_data(abs(floatval($sum_m3))/sqrt(pow(floatval($fb_m2),3)),4);
        if($ptxsa<0.43){
            $psxzt='正态分布';
        }else{
            $psxzt='有异常';
        }
        //==C99/POWER(C73,2)
        $ftxsb=sprint_data($sum_m4/pow($fb_m2,2),4);
        //2.27-3.87
        if($ftxsb>2.27 && $ftxsb<3.87){
            $ftxzt='正态分布';
        }else{
            $ftxzt='<font color=red>有异常</font>';
        }
        $html .= "<tr><td align=\"center\"></td><td>偏态系数A</td><td>{$ptxsa}</td><td>0.43</td><td>A&lt;A1</td><td>{$psxzt}</td><td></td><td></td><td></td><td></td></tr>";
        $html .= "<tr><td align=\"center\"></td><td>峰态系数B</td><td>{$ftxsb}</td><td>2.27-3.87</td><td>(B1&lt;B&lt;B1)</td><td>{$ftxzt}</td><td></td><td></td><td></td><td></td></tr>";

        $html.='</table>';
        echo $html;
    }

    //export_data_ten 10家
    public function export_data_ten(){

        $where=[];
        $where[]=['uid','=',get_login_admin('id')];
        $where[]=['type','=',2];
        $sjjylist=sjjylist::where($where)->order('id asc')->select();
        $i=0;
        $sum_avgs=0;
        $avg_arr=[];

        foreach($sjjylist as $key=>$v){
            $i++;
            //$rsd=round(floatval($v['rsd']),3);
            $sds=floatval($v['sds']);
            $avgs11=floatval($v['avgs']);
            $sum_avgs+=$avgs11;
            $avg_arr[]=floatval($avgs11);
            //=SMALL(C3:J3,1)
            $data_arr=[floatval($v['data1']),floatval($v['data2']),floatval($v['data3']),floatval($v['data4']),floatval($v['data5']),floatval($v['data6']),floatval($v['data7']),floatval($v['data8'])];
            //Log::info('data_arr='.json_encode($data_arr));
            $x1=Statistical::SMALL($data_arr,1);
            $x2=Statistical::SMALL($data_arr,2);
            $x3=Statistical::SMALL($data_arr,3);
            $x4=Statistical::SMALL($data_arr,4);
            $x5=Statistical::SMALL($data_arr,5);
            $x6=Statistical::SMALL($data_arr,6);
            $x7=Statistical::SMALL($data_arr,7);
            $x8=Statistical::SMALL($data_arr,8);
            $sjjylist[$key]['x1']=sprint_data($x1,2);
            $sjjylist[$key]['x2']=sprint_data($x2,2);
            $sjjylist[$key]['x3']=sprint_data($x3,2);
            $sjjylist[$key]['x4']=sprint_data($x4,2);
            $sjjylist[$key]['x5']=sprint_data($x5,2);
            $sjjylist[$key]['x6']=sprint_data($x6,2);
            $sjjylist[$key]['x7']=sprint_data($x7,2);
            $sjjylist[$key]['x8']=sprint_data($x8,2);
            //=(D17-C17)/(I17-C17)
            $r1=($x2-$x1)/($x7-$x1);
            $sjjylist[$key]['r1']=sprint_data($r1,3);
            //=(J17-I17)/(J17-D17)
            $r2=($x8-$x7)/($x8-$x2);
            $sjjylist[$key]['r2']=sprint_data($r2,3);
            //r1< f(5%,8)
            //且
            //rn< f(5%,8)
            //无异常值
            if($r1<0.608 && $r2<0.608){
                $zt='正常';
            }else{
                $zt='<font color=red>有异常值</font>';
            }
            $sjjylist[$key]['zt']=$zt;
            //=C17-$N$3
            //第2个表的数值
            $cc1=sprint_data($x1-$avgs11,2);
            $cc2=sprint_data($x2-$avgs11,2);
            $cc3=sprint_data($x3-$avgs11,2);
            $cc4=sprint_data($x4-$avgs11,2);
            $cc5=sprint_data($x5-$avgs11,2);
            $cc6=sprint_data($x6-$avgs11,2);
            $cc7=sprint_data($x7-$avgs11,2);
            $cc8=sprint_data($x8-$avgs11,2);
            $sjjylist[$key]['cc1']=$cc1;
            $sjjylist[$key]['cc2']=$cc2;
            $sjjylist[$key]['cc3']=$cc3;
            $sjjylist[$key]['cc4']=$cc4;
            $sjjylist[$key]['cc5']=$cc5;
            $sjjylist[$key]['cc6']=$cc6;
            $sjjylist[$key]['cc7']=$cc7;
            $sjjylist[$key]['cc8']=$cc8;
            //=K30*O3
            $cctxt=sprint_data(2.126*$sds,2);
            //$cctxt=0.01;
            $sjjylist[$key]['cctxt']=$cctxt;

        }
        $nums=10;
        $avg=$sum_avgs/$nums;
        //Log::info('range33='.json_encode($avg_arr));
        $sd = sprint_data(Statistical::STDEV($avg_arr),4);
        //=L3/K3
        $rsd=sprint_data($sd/$avg,4);
        //狄克逊法检验实验室间可疑值
        //可疑值
        //=SMALL($N$3:$N$11,1)
        $kyz['kyz1']=Statistical::SMALL($avg_arr,1);
        $kyz['kyz2']=Statistical::SMALL($avg_arr,2);
        $kyz['kyz3']=Statistical::SMALL($avg_arr,3);
        $kyz['kyz4']=Statistical::SMALL($avg_arr,4);
        $kyz['kyz5']=Statistical::SMALL($avg_arr,5);
        $kyz['kyz6']=Statistical::SMALL($avg_arr,6);
        $kyz['kyz7']=Statistical::SMALL($avg_arr,7);
        $kyz['kyz8']=Statistical::SMALL($avg_arr,8);
        $kyz['kyz9']=Statistical::SMALL($avg_arr,9);
        $kyz['kyz10']=Statistical::SMALL($avg_arr,10);
        //r1 =(D44-C44)/(K44-C44)
        $kyz['r1']=sprint_data(($kyz['kyz2']-$kyz['kyz1'])/($kyz['kyz9']-$kyz['kyz1']),3);
        //rn =(L44-K44)/(L44-D44)
        $kyz['rn']=sprint_data(($kyz['kyz10']-$kyz['kyz9'])/($kyz['kyz10']-$kyz['kyz2']),3);
        /*r1< f(5%,9)且rn< f(5%,9)无异常值*/
        if($kyz['r1']<0.53 && $kyz['rn']<0.53){
            $kyz['zt']='无异常';
        }else{
            $kyz['zt']='<font color=red>有异常</font>';
        }
        //mean =AVERAGE(C44:L44)
        $kzy_range=[$kyz['kyz1'],$kyz['kyz2'],$kyz['kyz3'],$kyz['kyz4'],$kyz['kyz5'],$kyz['kyz6'],$kyz['kyz7'],$kyz['kyz8'],$kyz['kyz9'],$kyz['kyz10']];
        $kyz['mean']=sprint_data(Statistical::AVERAGE($kzy_range),3);
        //SD =STDEV(C44:L44)
        $kyz['sd']=sprint_data(Statistical::STDEV($kzy_range),8);
        //格拉布斯法检验实验室内异常值
        //=C42-$Q$42
        $ycz['ycz1']=sprint_data($kyz['kyz1']-$kyz['mean'],3);
        $ycz['ycz2']=sprint_data($kyz['kyz2']-$kyz['mean'],3);
        $ycz['ycz3']=sprint_data($kyz['kyz3']-$kyz['mean'],3);
        $ycz['ycz4']=sprint_data($kyz['kyz4']-$kyz['mean'],3);
        $ycz['ycz5']=sprint_data($kyz['kyz5']-$kyz['mean'],3);
        $ycz['ycz6']=sprint_data($kyz['kyz6']-$kyz['mean'],3);
        $ycz['ycz7']=sprint_data($kyz['kyz7']-$kyz['mean'],3);
        $ycz['ycz8']=sprint_data($kyz['kyz8']-$kyz['mean'],3);
        $ycz['ycz9']=sprint_data($kyz['kyz9']-$kyz['mean'],3);
        $ycz['ycz10']=sprint_data($kyz['kyz10']-$kyz['mean'],3);
        //λ(0.05, 10) 2.29
        $ycz11=2.290;
        $ycz['ycz11']=$ycz11;
        //λ(a,n)*s =M48*S44
        $ycz12=sprint_data($ycz11*$kyz['sd'],4);
        $ycz['ycz12']=$ycz12;
        //科克伦法实验室间数据组等精度检验
        //=O3^2
        $jdjy_array=sjjylist::where($where)->order('id asc')->column('sds');
        //$sds_max=sjjylist::where($where)->order('id asc')->max('sds');
        //$sds_min=sjjylist::where($where)->order('id asc')->min('sds');
        $jdjy['sz1']=sprint_data(pow($jdjy_array[0],2),4);
        $jdjy['sz2']=sprint_data(pow($jdjy_array[1],2),4);
        $jdjy['sz3']=sprint_data(pow($jdjy_array[2],2),4);
        $jdjy['sz4']=sprint_data(pow($jdjy_array[3],2),4);
        $jdjy['sz5']=sprint_data(pow($jdjy_array[4],2),4);
        $jdjy['sz6']=sprint_data(pow($jdjy_array[5],2),4);
        $jdjy['sz7']=sprint_data(pow($jdjy_array[6],2),4);
        $jdjy['sz8']=sprint_data(pow($jdjy_array[7],2),4);
        $jdjy['sz9']=sprint_data(pow($jdjy_array[8],2),4);
        $jdjy['sz10']=sprint_data(pow($jdjy_array[9],2),4);
        //=MAX(C50:K50)
        //Log::info('jdjy_array='.json_encode($jdjy_array));
        $jdjy_array2=[floatval($jdjy['sz1']),floatval($jdjy['sz2']),floatval($jdjy['sz3']),floatval($jdjy['sz4']),floatval($jdjy['sz5']),floatval($jdjy['sz6']),floatval($jdjy['sz7']),floatval($jdjy['sz8']),floatval($jdjy['sz9']),floatval($jdjy['sz10'])];
        $jdjy['max']=Statistical::MAX($jdjy_array2);//最大值
        $jdjy['sum']=sprint_data(getSum($jdjy_array2),4);//求和
        //=L50/M50 科克伦统计量C
        if($jdjy['sum']>0){
            $jdjy['c']=sprint_data($jdjy['max']/$jdjy['sum'],4);
        }else{
            $jdjy['c']=0;
        }
        //C(0.05，9，8) 0.2768
        $jdjy['c2']=0.2768;
        //C< C(0.05，9，8)
        //实验室间数据平均值等精度
        if($jdjy['c']<$jdjy['c2']){
            $jdjy['zt']='正常';
        }else{
            $jdjy['zt']='不满足';
        }
        //一致性检验
        //=MAX(N3:N11)
        //=MIN(N3:N11)
        $yzx['max']=sprint_data(Statistical::MAX($avg_arr),2);
        $yzx['min']=sprint_data(Statistical::MIN($avg_arr),2);
        //=O3
        $yzx['max_sds']=sprint_data(floatval($jdjy_array[0]),4);
        //=O7
        $yzx['min_sds']=sprint_data(floatval($jdjy_array[4]),4);
        //=COUNT(C3:J3)
        $yzx['count']=8;
        //=(D55-D56)/(((((E55^2)*(E57-1))+((E56^2)*(E57-1)))/((E57*2-2)))*((E57+E57)/(E57*E57)))^0.5
        $ttt1=pow($yzx['max_sds'],2);
        $ttt2=pow($yzx['min_sds'],2);
        $yzxss2=((((($ttt1)*($yzx['count']-1))+(($ttt2)*($yzx['count']-1)))/(($yzx['count']*2-2)))*(($yzx['count']+$yzx['count'])/($yzx['count']*$yzx['count'])));
        $yzxss21=pow($yzxss2,0.5);
        $yzx['yzxss']=($yzx['max']-$yzx['min'])/$yzxss21;
        //临界值 =TINV(0.05,E55*2-2)
        $ljz=Statistical::TINV(0.05,$yzx['count']*2-2);
        $yzx['ljz']=sprint_data($ljz,9);


        //导出表格
        $objExcel = new Spreadsheet();
        $objWriter = IOFactory::createWriter($objExcel, 'Xlsx');
        $objActSheet = $objExcel->getActiveSheet(0);
        $objActSheet->setTitle('数据检验10家分析');//设置excel的标题

        $objActSheet->getRowDimension(1)->setRowHeight(25);
        //$objActSheet->getStyle('A1:F1')->getFont()->setSize(14);
        //$objActSheet->getStyle('A1:F1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('CCCCCC');
        // 设置列宽
        $objActSheet->getColumnDimension('A')->setWidth(15);
        $objActSheet->getColumnDimension('B')->setWidth(15);
        $objActSheet->getColumnDimension('C')->setWidth(15);
        $objActSheet->getColumnDimension('D')->setWidth(15);
        $objActSheet->getColumnDimension('E')->setWidth(15);
        $objActSheet->getColumnDimension('F')->setWidth(15);
        $objActSheet->getColumnDimension('G')->setWidth(15);
        $objActSheet->getColumnDimension('H')->setWidth(15);
        $objActSheet->getColumnDimension('I')->setWidth(15);
        $objActSheet->getColumnDimension('J')->setWidth(15);
        $objActSheet->getColumnDimension('K')->setWidth(15);
        $objActSheet->getColumnDimension('L')->setWidth(15);
        $objActSheet->getColumnDimension('M')->setWidth(15);
        $objActSheet->getColumnDimension('N')->setWidth(15);
        // 设置行高

        //设置水平、垂直居中
        $styleArray2 = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];
        $styleArray3 = [
            'font' => [
                'name' => 'Arial',
                'bold' => true,
                'italic' => false,
                'underline' => false,
                'strikethrough' => false,
                'superscript' => true, // 设置为下标
                'color' => [
                    'rgb' => Color::COLOR_BLACK
                ]
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];
        //样式变量
        $styleArray = [
            //设置字体样式
            'font' => [
                'name' => 'Arial',
                //'bold' => true,
                //'italic' => false,
                //'underline' => Font::UNDERLINE_DOUBLE,
                //'strikethrough' => false,
                'color' => [
                    'rgb' => Color::COLOR_BLACK
                ]
            ],
            //设置边框线样式
            'borders' => [
                //allBorders所有的边框线样式
                //左边框线
                /*'bottom' => [
                    'borderStyle' => Border::BORDER_DASHDOT,
                    'color' => [
                        'rgb' => '808080'
                    ]
                ],*/
                //上边框线
                /*'top' => [
                    'borderStyle' => Border::BORDER_DASHDOT,
                    'color' => [
                        'rgb' => '808080'
                    ]
                ]*/
            ],
            //对齐样式
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
                'wrapText' => true,
            ],
            //是否使用前缀
            'quotePrefix'    => true
        ];

        $objActSheet->getStyle('A1:F11')->applyFromArray($styleArray);
        $objActSheet->setCellValue('A1', '平均值');
        $objActSheet->setCellValue('B1', 'SD');
        $objActSheet->setCellValue('C1', 'RSD(%)');
        $objActSheet->setCellValue('D1', '平均值');
        $objActSheet->setCellValue('E1', 'SD');
        $objActSheet->setCellValue('F1', 'RSD(%)');
        $objActSheet->getStyle('A1'  . ':F11')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式

        $baseRow = 2; //数据从N-1行开始往下输出
        foreach ($sjjylist as $r => $h) {
            $avgs11=sprint_data(floatval($h['avgs']),3);
            $sds11=sprint_data(floatval($h['sds']),3);
            $rsd11=sprint_data(floatval($h['rsd']),3);
            $i = $baseRow + $r;
            $objActSheet->getRowDimension($i)->setRowHeight(25);
            $objActSheet->getStyle('A' . $i . ':C' . $i)->getFont()->setSize(12);
            $objActSheet->setCellValue('A' . $i, '');
            $objActSheet->setCellValue('B' . $i, '');
            $objActSheet->setCellValue('C' . $i, '');
            $objActSheet->setCellValue('D' . $i, $avgs11);
            $objActSheet->setCellValue('E' . $i, $sds11);
            $objActSheet->setCellValue('F' . $i, $rsd11);
        }
        // 合并单元格
        $objActSheet->mergeCells('A2' .':A11');            // 合并单元格
        $objActSheet->mergeCells('B2' .':B11');            // 合并单元格
        $objActSheet->mergeCells('C2' .':C11');
        // 设置合并后的单元格的值
        $objActSheet->setCellValue('A2', $avg);
        $objActSheet->setCellValue('B2', $sd);
        $objActSheet->setCellValue('C2', $rsd);

        $objActSheet->getStyle('A15:M26')->applyFromArray($styleArray);

        //狄克逊法检验实验室内可疑值
        $objActSheet->mergeCells('A15' .':N15');
        $objActSheet->getStyle('A15')->getFont()->setSize(14);
        $objActSheet->getStyle('A15')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('CCCCCC');
        $objActSheet->setCellValue('A15', '狄克逊法检验实验室内可疑值');


        //$xb1='x<sub>(1)</sub>';
        $objActSheet->setCellValue('A16', '实验室');
        $objActSheet->setCellValue('B16', 'x(1)');
        //$objActSheet->getStyle('B16')->getFont()->setSuperscript(true);

        $objActSheet->setCellValue('C16', 'x(2)');
        //$objActSheet->getStyle('C16')->getFont()->setSuperscript(false);
        //$objActSheet->getStyle('C16')->getFont()->setSubscript(true);
        $objActSheet->setCellValue('D16', 'x(3)');
        $objActSheet->setCellValue('E16', 'x(4)');
        $objActSheet->setCellValue('F16', 'x(5)');
        $objActSheet->setCellValue('G16', 'x(6)');
        $objActSheet->setCellValue('H16', 'x(7)');
        $objActSheet->setCellValue('I16', 'x(8)');
        $objActSheet->setCellValue('J16', 'r(1)');
        $objActSheet->setCellValue('K16', 'r(n)');
        $objActSheet->setCellValue('L16', 'f(0.05,8)');
        $objActSheet->setCellValue('M16', '判断');

        $objActSheet->getStyle('A16'  . ':N26')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
        //合并单元格
        $objActSheet->mergeCells('N16' .':N26');

        $objActSheet->setCellValue('N16', '如果不满足要求，则将不满足判断要求的异常值用红色显示');
        $objActSheet->getStyle('N16')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('FF0000');
        $objActSheet->getStyle('N16')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLACK);//将A1单元格文字颜色设为红色
        $objActSheet->getStyle('N16')->applyFromArray($styleArray2);
        //文本内容自动换行
        $objActSheet->getStyle('N16')->getAlignment()->setWrapText(true);

        if ($sjjylist) {
            $baseRow2 = 17; //数据从N-1行开始往下输出
            foreach ($sjjylist as $r => $h) {
                $i = $baseRow2 + $r;
                $objActSheet->getRowDimension($i)->setRowHeight(25);
                $objActSheet->setCellValue('A' . $i, $h['nums']);
                $objActSheet->setCellValue('B' . $i, $h['x1']);
                $objActSheet->setCellValue('C' . $i, $h['x2']);
                $objActSheet->setCellValue('D' . $i, $h['x3']);
                $objActSheet->setCellValue('E' . $i, $h['x4']);
                $objActSheet->setCellValue('F' . $i, $h['x5']);
                $objActSheet->setCellValue('G' . $i, $h['x6']);
                $objActSheet->setCellValue('H' . $i, $h['x7']);
                $objActSheet->setCellValue('I' . $i, $h['x8']);
                $objActSheet->setCellValue('J' . $i, $h['r1']);
                $objActSheet->setCellValue('K' . $i, $h['r2']);
                $objActSheet->setCellValue('L' . $i, '0.608');
                $objActSheet->setCellValue('M' . $i, $h['zt']);
                $objActSheet->setCellValue('N' . $i, '');
                //数据异常的显示红色
                if($h['r1']>0.608 ){
                    $objActSheet->getStyle('J'. $i)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
                }
                if($h['r2']>0.608){
                    $objActSheet->getStyle('K'. $i)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
                }
                if($h['zt']=='有异常值'){
                    $objActSheet->getStyle('M'. $i)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色

                }
            }
        }
        //格拉布斯法检验实验室内异常值
        //合并单元格
        $objActSheet->mergeCells('A28' .':M28');
        $objActSheet->getStyle('A28')->getFont()->setSize(14);
        $objActSheet->getStyle('A28')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('CCCCCC');
        $objActSheet->setCellValue('A28', '格拉布斯法检验实验室内异常值');
        $objActSheet->mergeCells('B29' .':I29');
        $objActSheet->mergeCells('L31' .':L40');
        $objActSheet->mergeCells('M29' .':M40');

        //测量值残差vi
        $objActSheet->setCellValue('B29', '测量值残差vi');

        //文本内容自动换行
        $objActSheet->getStyle('L31')->getAlignment()->setWrapText(true);
        $objActSheet->getStyle('M29')->getAlignment()->setWrapText(true);
        $objActSheet->setCellValue('M29', '如果不满足要求，则将不满足判断要求的异常值用红色显示');
        $objActSheet->getStyle('M29')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('FF0000');
        $objActSheet->getStyle('M29')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLACK);//将A1单元格文字颜色设为红色
        $objActSheet->getStyle('M29')->applyFromArray($styleArray2);

        $objActSheet->mergeCells('A29' .':A30');
        $objActSheet->mergeCells('J29' .':J30');
        $objActSheet->mergeCells('K29' .':K30');
        $objActSheet->mergeCells('L29' .':L30');


        $objActSheet->setCellValue('A29', '实验室');
        $objActSheet->setCellValue('J29', 'λ(0.05, 8)');
        $objActSheet->setCellValue('K29', 'λ(a，n)*s');
        $objActSheet->setCellValue('L29', '判断');

        $objActSheet->setCellValue('B30', '1');
        $objActSheet->setCellValue('C30', '2');
        $objActSheet->setCellValue('D30', '3');
        $objActSheet->setCellValue('E30', '4');
        $objActSheet->setCellValue('F30', '5');
        $objActSheet->setCellValue('G30', '6');
        $objActSheet->setCellValue('H30', '7');
        $objActSheet->setCellValue('I30', '8');

        $objActSheet->getStyle('A28'  . ':M40')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
        $objActSheet->getStyle('A28:L40')->applyFromArray($styleArray);
        if ($sjjylist) {
            $baseRow2 = 31; //数据从N-1行开始往下输出
            foreach ($sjjylist as $r => $h) {
                $i = $baseRow2 + $r;
                $objActSheet->getRowDimension($i)->setRowHeight(25);
                $objActSheet->setCellValue('A' . $i, $h['nums']);
                $objActSheet->setCellValue('B' . $i, $h['cc1']);
                $objActSheet->setCellValue('C' . $i, $h['cc2']);
                $objActSheet->setCellValue('D' . $i, $h['cc3']);
                $objActSheet->setCellValue('E' . $i, $h['cc4']);
                $objActSheet->setCellValue('F' . $i, $h['cc5']);
                $objActSheet->setCellValue('G' . $i, $h['cc6']);
                $objActSheet->setCellValue('H' . $i, $h['cc7']);
                $objActSheet->setCellValue('I' . $i, $h['cc8']);
                $objActSheet->setCellValue('J' . $i, 2.126);
                $objActSheet->setCellValue('K' . $i, $h['cctxt']);
                $objActSheet->setCellValue('L' . $i, '∣vi∣< λ(a，n)*s');

                //数据异常的显示红色
                if($h['cc1']>$h['cctxt'] ){
                    $objActSheet->getStyle('B'. $i)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
                }
                if($h['cc2']>$h['cctxt'] ){
                    $objActSheet->getStyle('C'. $i)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
                }
                if($h['cc3']>$h['cctxt'] ){
                    $objActSheet->getStyle('D'. $i)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
                }if($h['cc4']>$h['cctxt'] ){
                    $objActSheet->getStyle('E'. $i)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
                }
                if($h['cc5']>$h['cctxt'] ){
                    $objActSheet->getStyle('F'. $i)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
                }
                if($h['cc6']>$h['cctxt'] ){
                    $objActSheet->getStyle('G'. $i)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
                }
                if($h['cc7']>$h['cctxt'] ){
                    $objActSheet->getStyle('H'. $i)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
                }
                if($h['cc8']>$h['cctxt'] ){
                    $objActSheet->getStyle('I'. $i)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
                }
            }
        }

        //狄克逊法检验实验室间可疑值
        //合并单元格
        $objActSheet->mergeCells('A42' .':Q42');
        $objActSheet->mergeCells('O43' .':O44');
        $objActSheet->getStyle('A42')->getFont()->setSize(14);
        $objActSheet->getStyle('A42')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('CCCCCC');
        $objActSheet->setCellValue('A42', '狄克逊法检验实验室间可疑值');

        //文本内容自动换行
        $objActSheet->getStyle('O43')->getAlignment()->setWrapText(true);
        $objActSheet->setCellValue('O43', '如果不满足要求，则将不满足判断要求的异常值用红色显示');
        $objActSheet->getStyle('O43')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('FF0000');
        $objActSheet->getStyle('O43')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLACK);//将A1单元格文字颜色设为红色
        $objActSheet->getStyle('O43')->applyFromArray($styleArray2);


        $objActSheet->setCellValue('A43', '平均值从小到大排列');
        $objActSheet->setCellValue('B43', '¯x(1)');
        $objActSheet->setCellValue('C43', '¯x(2)');
        $objActSheet->setCellValue('D43', '¯x(3)');
        $objActSheet->setCellValue('E43', '¯x(4)');
        $objActSheet->setCellValue('F43', '¯x(5)');
        $objActSheet->setCellValue('G43', '¯x(6)');
        $objActSheet->setCellValue('H43', '¯x(7)');
        $objActSheet->setCellValue('I43', '¯x(8)');
        $objActSheet->setCellValue('J43', '¯x(9)');
        $objActSheet->setCellValue('K43', '¯x(10)');
        $objActSheet->setCellValue('L43', 'r1');
        $objActSheet->setCellValue('M43', 'rn');
        $objActSheet->setCellValue('N43', 'f(0.05,10)');
        $objActSheet->setCellValue('O43', '判断');
        $objActSheet->setCellValue('P43', 'mean');
        $objActSheet->setCellValue('Q43', 'SD');

        $objActSheet->getStyle('A42'  . ':Q44')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
        $objActSheet->getStyle('A42:Q44')->applyFromArray($styleArray);

        $objActSheet->setCellValue('A44', '');
        $objActSheet->setCellValue('B44', $kyz['kyz1']);
        $objActSheet->setCellValue('C44', $kyz['kyz2']);
        $objActSheet->setCellValue('D44', $kyz['kyz3']);
        $objActSheet->setCellValue('E44', $kyz['kyz4']);
        $objActSheet->setCellValue('F44', $kyz['kyz5']);
        $objActSheet->setCellValue('G44', $kyz['kyz6']);
        $objActSheet->setCellValue('H44', $kyz['kyz7']);
        $objActSheet->setCellValue('I44', $kyz['kyz8']);
        $objActSheet->setCellValue('J44', $kyz['kyz9']);
        $objActSheet->setCellValue('K44', $kyz['kyz10']);
        $objActSheet->setCellValue('L44', $kyz['r1']);
        $objActSheet->setCellValue('M44', $kyz['rn']);
        $objActSheet->setCellValue('N44', '0.53');
        $objActSheet->setCellValue('O44', 'r1< f(5%,10)rn< f(5%,10)无异常值');
        //$objActSheet->setCellValue('O43', '如果不满足要求，则将不满足判断要求的异常值用红色显示');
        $objActSheet->setCellValue('P44', $kyz['mean']);
        $objActSheet->setCellValue('Q44', $kyz['sd']);
        //如果有异常则加红
        if($kyz['r1']>0.53 && $kyz['rn'] >0.53){
            $objActSheet->getStyle('L44')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
            $objActSheet->getStyle('M44')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
        }
        $objActSheet->getRowDimension(44)->setRowHeight(50);

        //格拉布斯法检验实验室内异常值
        // {$ycz['ycz1']}
        //合并单元格
        $objActSheet->mergeCells('A46' .':N46');
        $objActSheet->getStyle('A46')->getFont()->setSize(14);
        $objActSheet->getStyle('A46')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('CCCCCC');
        $objActSheet->setCellValue('A46', '格拉布斯法检验实验室内异常值 检验9家实验室间数据');

        $objActSheet->mergeCells('A47' .':J47');
        $objActSheet->setCellValue('A47', '测量值残差vi');
        $objActSheet->mergeCells('K47' .':K48');
        $objActSheet->mergeCells('L47' .':L48');
        $objActSheet->mergeCells('M47' .':M48');
        $objActSheet->mergeCells('N47' .':N49');

        $objActSheet->setCellValue('K47', 'λ(0.05, 9)');
        $objActSheet->setCellValue('L47', 'λ(a,n)*s');
        $objActSheet->setCellValue('M47', '判断');

        $objActSheet->getStyle('N47')->getAlignment()->setWrapText(true);
        $objActSheet->setCellValue('N47', '如果不满足要求，则将不满足判断要求的异常值用红色显示');
        $objActSheet->getStyle('N47')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('FF0000');
        $objActSheet->getStyle('N47')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLACK);//将A1单元格文字颜色设为红色
        $objActSheet->getStyle('N47')->applyFromArray($styleArray2);


        $objActSheet->setCellValue('A48', '1');
        $objActSheet->setCellValue('B48', '2');
        $objActSheet->setCellValue('C48', '3');
        $objActSheet->setCellValue('D48', '4');
        $objActSheet->setCellValue('E48', '5');
        $objActSheet->setCellValue('F48', '6');
        $objActSheet->setCellValue('G48', '7');
        $objActSheet->setCellValue('H48', '8');
        $objActSheet->setCellValue('I48', '9');
        $objActSheet->setCellValue('J48', '10');

        $objActSheet->getStyle('A46'  . ':N49')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
        $objActSheet->getStyle('A46:N49')->applyFromArray($styleArray);

        $objActSheet->setCellValue('A49', $ycz['ycz1']);
        $objActSheet->setCellValue('B49', $ycz['ycz2']);
        $objActSheet->setCellValue('C49', $ycz['ycz3']);
        $objActSheet->setCellValue('D49', $ycz['ycz4']);
        $objActSheet->setCellValue('E49', $ycz['ycz5']);
        $objActSheet->setCellValue('F49', $ycz['ycz6']);
        $objActSheet->setCellValue('G49', $ycz['ycz7']);
        $objActSheet->setCellValue('H49', $ycz['ycz8']);
        $objActSheet->setCellValue('I49', $ycz['ycz9']);
        $objActSheet->setCellValue('J49', $ycz['ycz10']);
        $objActSheet->setCellValue('K49', $ycz['ycz11']);
        $objActSheet->setCellValue('L49', $ycz['ycz12']);
        $objActSheet->setCellValue('M49', '∣vi∣< λ(a n)*s');

        if($ycz['ycz1']>$ycz['ycz11'] ){
            $objActSheet->getStyle('A49')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
        }
        if($ycz['ycz2']>$ycz['ycz11'] ){
            $objActSheet->getStyle('B49')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
        }
        if($ycz['ycz3']>$ycz['ycz11'] ){
            $objActSheet->getStyle('C49')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
        }
        if($ycz['ycz4']>$ycz['ycz11'] ){
            $objActSheet->getStyle('D49')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
        }
        if($ycz['ycz5']>$ycz['ycz11'] ){
            $objActSheet->getStyle('E49')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
        }
        if($ycz['ycz6']>$ycz['ycz11'] ){
            $objActSheet->getStyle('F49')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
        }
        if($ycz['ycz7']>$ycz['ycz11'] ){
            $objActSheet->getStyle('G49')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
        }
        if($ycz['ycz8']>$ycz['ycz11'] ){
            $objActSheet->getStyle('H49')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
        }
        if($ycz['ycz9']>$ycz['ycz11'] ){
            $objActSheet->getStyle('I49')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
        }

        //科克伦法实验室间数据组等精度检验
        //{$jdjy['sz1']}
        //合并单元格
        $objActSheet->mergeCells('A51' .':P51');
        $objActSheet->getStyle('A51')->getFont()->setSize(14);
        $objActSheet->getStyle('A51')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('CCCCCC');
        $objActSheet->setCellValue('A51', '科克伦法实验室间数据组等精度检验');
        $objActSheet->mergeCells('A52' .':J52');
        $objActSheet->setCellValue('A52', '实验室方差');


        $objActSheet->mergeCells('K52' .':K53');
        $objActSheet->mergeCells('L52' .':L53');
        $objActSheet->mergeCells('M52' .':M53');
        $objActSheet->mergeCells('N52' .':N53');
        $objActSheet->mergeCells('O52' .':O53');
        $objActSheet->mergeCells('P52' .':P54');
        //文本内容自动换行
        $objActSheet->getStyle('P52')->getAlignment()->setWrapText(true);
        $objActSheet->setCellValue('P52', '如果不满足要求，则将不满足判断要求的异常值用红色显示');
        $objActSheet->getStyle('P52')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('FF0000');
        $objActSheet->getStyle('P52')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLACK);//将A1单元格文字颜色设为红色
        $objActSheet->getStyle('P52')->applyFromArray($styleArray2);


        $objActSheet->setCellValue('K52', 's2max');
        $objActSheet->setCellValue('L52', '实验室方差和');
        $objActSheet->setCellValue('M52', '科克伦统计量C');
        $objActSheet->setCellValue('N52', 'C(0.05，10，8)');
        $objActSheet->setCellValue('O52', '判断');

        $objActSheet->setCellValue('A53', 'S平方(1)');
        $objActSheet->setCellValue('B53', 'S平方(2)');
        $objActSheet->setCellValue('C53', 'S平方(3)');
        $objActSheet->setCellValue('D53', 'S平方(4)');
        $objActSheet->setCellValue('E53', 'S平方(5)');
        $objActSheet->setCellValue('F53', 'S平方(6)');
        $objActSheet->setCellValue('G53', 'S平方(7)');
        $objActSheet->setCellValue('H53', 'S平方(8)');
        $objActSheet->setCellValue('I53', 'S平方(9)');
        $objActSheet->setCellValue('J53', 'S平方(10)');

        $objActSheet->getStyle('A51'  . ':P54')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
        $objActSheet->getStyle('A51:P54')->applyFromArray($styleArray);


        $objActSheet->setCellValue('A54', $jdjy['sz1']);
        $objActSheet->setCellValue('B54', $jdjy['sz2']);
        $objActSheet->setCellValue('C54', $jdjy['sz3']);
        $objActSheet->setCellValue('D54', $jdjy['sz4']);
        $objActSheet->setCellValue('E54', $jdjy['sz5']);
        $objActSheet->setCellValue('F54', $jdjy['sz6']);
        $objActSheet->setCellValue('G54', $jdjy['sz7']);
        $objActSheet->setCellValue('H54', $jdjy['sz8']);
        $objActSheet->setCellValue('I54', $jdjy['sz9']);
        $objActSheet->setCellValue('J54', $jdjy['sz10']);
        $objActSheet->setCellValue('K54', $jdjy['max']);
        $objActSheet->setCellValue('L54', $jdjy['sum']);
        $objActSheet->setCellValue('M54', $jdjy['c']);
        $objActSheet->setCellValue('N54', '0.2768');
        $objActSheet->setCellValue('O54', 'C< C(0.05，10，8)实验室间数据平均值等精度');
        $objActSheet->setCellValue('P54', '');

        if(floatval($jdjy['c'])>0.2768){
            $objActSheet->getStyle('M54')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//将A1单元格文字颜色设为红色
        }

        $objActSheet->getRowDimension(54)->setRowHeight(100);
        //一致性检验 t-检验: 双样本等方差假设

       //合并单元格
       $objActSheet->mergeCells('A56' .':E56');
       $objActSheet->getStyle('A56')->getFont()->setSize(14);
       $objActSheet->getStyle('A56')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('CCCCCC');
       $objActSheet->setCellValue('A56', '一致性检验 t-检验: 双样本等方差假设');

       $objActSheet->setCellValue('A57', '');
       $objActSheet->setCellValue('B57', '平均值');
       $objActSheet->setCellValue('C57', 'SD');
       $objActSheet->setCellValue('D57', '平均值一致性检验-t检验法');
       $objActSheet->setCellValue('E57', '临界值');
       $objActSheet->getStyle('A56'  . ':E60')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
       $objActSheet->getStyle('A56:E60')->applyFromArray($styleArray);

       $objActSheet->setCellValue('A58', '最大值');
       $objActSheet->setCellValue('B58', $yzx['max']);
       //$objActSheet->setCellValue('B57', '=SUM(D2:D10)');
       $objActSheet->setCellValue('C58', $yzx['max_sds']);
       $objActSheet->setCellValue('D58', '');
       $objActSheet->setCellValue('E58', '');

       $objActSheet->setCellValue('A59', '最小值');
       $objActSheet->setCellValue('B59', $yzx['min']);
       $objActSheet->setCellValue('C59', $yzx['min_sds']);
       $objActSheet->setCellValue('D59', '');
       $objActSheet->setCellValue('E59', '');

       $objActSheet->setCellValue('A60', '');
       $objActSheet->setCellValue('B60', '实验数');
       $objActSheet->setCellValue('C60', $yzx['count']);

       $objActSheet->setCellValue('D60', $yzx['yzxss']);
       $objActSheet->setCellValue('E60', $yzx['ljz']);

       //偏态系数和峰态系数法
       //合并单元格
       $objActSheet->mergeCells('A65' .':J65');
       $objActSheet->getStyle('A65')->getFont()->setSize(14);
       $objActSheet->getStyle('A65')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('CCCCCC');
       $objActSheet->setCellValue('A65', '偏态系数和峰态系数法 正态分布检验');
       //
       $objActSheet->setCellValue('A66', '残差');
       $objActSheet->setCellValue('B66', '实验室');
       $objActSheet->setCellValue('C66', '1');
       $objActSheet->setCellValue('D66', '2');
       $objActSheet->setCellValue('E66', '3');
       $objActSheet->setCellValue('F66', '4');
       $objActSheet->setCellValue('G66', '5');
       $objActSheet->setCellValue('H66', '6');
       $objActSheet->setCellValue('I66', '7');
       $objActSheet->setCellValue('J66', '8');

       $objActSheet->getStyle('A65'  . ':J77')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
       $objActSheet->getStyle('A66:J77')->applyFromArray($styleArray);

       $fb_array=[];
       if ($sjjylist) {
           $baseRow2 = 67; //数据从N-1行开始往下输出
           foreach ($sjjylist as $key => $h) {
               $i = $baseRow2 + $key;
               $objActSheet->getRowDimension($i)->setRowHeight(25);

               $jy1=$h['data1']-$avg;
               $jy2=$h['data2']-$avg;
               $jy3=$h['data3']-$avg;
               $jy4=$h['data4']-$avg;
               $jy5=$h['data5']-$avg;
               $jy6=$h['data6']-$avg;
               $jy7=$h['data7']-$avg;
               $jy8=$h['data8']-$avg;
               $fb_array[]=$jy1;
               $fb_array[]=$jy2;
               $fb_array[]=$jy3;
               $fb_array[]=$jy4;
               $fb_array[]=$jy5;
               $fb_array[]=$jy6;
               $fb_array[]=$jy7;
               $fb_array[]=$jy8;
               //
               $lf1=pow($jy1,3);
               $lf2=pow($jy2,3);
               $lf3=pow($jy3,3);
               $lf4=pow($jy4,3);
               $lf5=pow($jy5,3);
               $lf6=pow($jy6,3);
               $lf7=pow($jy7,3);
               $lf8=pow($jy8,3);
               $sjjylist[$key]['lf1']=sprint_data($lf1,5);
               $sjjylist[$key]['lf2']=sprint_data($lf2,5);
               $sjjylist[$key]['lf3']=sprint_data($lf3,5);
               $sjjylist[$key]['lf4']=sprint_data($lf4,5);
               $sjjylist[$key]['lf5']=sprint_data($lf5,5);
               $sjjylist[$key]['lf6']=sprint_data($lf6,5);
               $sjjylist[$key]['lf7']=sprint_data($lf7,5);
               $sjjylist[$key]['lf8']=sprint_data($lf8,5);
               //
               $jy11=sprint_data($jy1,4);
               $jy21=sprint_data($jy2,4);
               $jy31=sprint_data($jy3,4);
               $jy41=sprint_data($jy4,4);
               $jy51=sprint_data($jy5,4);
               $jy61=sprint_data($jy6,4);
               $jy71=sprint_data($jy7,4);
               $jy81=sprint_data($jy8,4);

               //
               $lb1=pow($jy1,4);
               $lb2=pow($jy2,4);
               $lb3=pow($jy3,4);
               $lb4=pow($jy4,4);
               $lb5=pow($jy5,4);
               $lb6=pow($jy6,4);
               $lb7=pow($jy7,4);
               $lb8=pow($jy8,4);
               $sjjylist[$key]['lb1']=sprint_data($lb1,5);
               $sjjylist[$key]['lb2']=sprint_data($lb2,5);
               $sjjylist[$key]['lb3']=sprint_data($lb3,5);
               $sjjylist[$key]['lb4']=sprint_data($lb4,5);
               $sjjylist[$key]['lb5']=sprint_data($lb5,5);
               $sjjylist[$key]['lb6']=sprint_data($lb6,5);
               $sjjylist[$key]['lb7']=sprint_data($lb7,5);
               $sjjylist[$key]['lb8']=sprint_data($lb8,5);

               $objActSheet->setCellValue('A'.$i, '');
               $objActSheet->setCellValue('B'.$i, $h['nums']);
               $objActSheet->setCellValue('C'.$i, $jy11);
               $objActSheet->setCellValue('D'.$i, $jy21);
               $objActSheet->setCellValue('E'.$i, $jy31);
               $objActSheet->setCellValue('F'.$i, $jy41);
               $objActSheet->setCellValue('G'.$i, $jy51);
               $objActSheet->setCellValue('H'.$i, $jy61);
               $objActSheet->setCellValue('I'.$i, $jy71);
               $objActSheet->setCellValue('J'.$i, $jy81);
           }
       }

       $sum_fb=sum_pingfang($fb_array);
       $count_fb=count($fb_array);
       $fb_m2=truncateDecimal($sum_fb/$count_fb,4);
       $objActSheet->getRowDimension(77)->setRowHeight(25);
       $objActSheet->setCellValue('A77', '');
       $objActSheet->setCellValue('B77', 'm2');
       $objActSheet->setCellValue('C77', $fb_m2);
       $objActSheet->setCellValue('D77', '');
       $objActSheet->setCellValue('E77', '');
       $objActSheet->setCellValue('F77', '');
       $objActSheet->setCellValue('G77', '');
       $objActSheet->setCellValue('H77', '');
       $objActSheet->setCellValue('I77', '');
       $objActSheet->setCellValue('J77', '');

       //
       //79
       $objActSheet->setCellValue('A79', '');
       $objActSheet->setCellValue('B79', 'Lab');
       $objActSheet->setCellValue('C79', '1');
       $objActSheet->setCellValue('D79', '2');
       $objActSheet->setCellValue('E79', '3');
       $objActSheet->setCellValue('F79', '4');
       $objActSheet->setCellValue('G79', '5');
       $objActSheet->setCellValue('H79', '6');
       $objActSheet->setCellValue('I79', '7');
       $objActSheet->setCellValue('J79', '8');

       $objActSheet->getRowDimension(79)->setRowHeight(25);

       $objActSheet->getStyle('A79'  . ':J90')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
       $objActSheet->getStyle('A79:J90')->applyFromArray($styleArray);

       $fb2_array=[];
       if ($sjjylist) {
           $baseRow3 = 80; //数据从N-1行开始往下输出
           foreach ($sjjylist as $key => $h) {
               $i = $baseRow3 + $key;
               $fb2_array[]=$h['lf1'];
               $fb2_array[]=$h['lf2'];
               $fb2_array[]=$h['lf3'];
               $fb2_array[]=$h['lf4'];
               $fb2_array[]=$h['lf5'];
               $fb2_array[]=$h['lf6'];
               $fb2_array[]=$h['lf7'];
               $fb2_array[]=$h['lf8'];
               $objActSheet->getRowDimension($i)->setRowHeight(25);
               //Log::info('lf1='.$h['lf1']);
               $objActSheet->setCellValue('A'.$i, '');
               $objActSheet->setCellValue('B'.$i, $h['nums']);
               $objActSheet->setCellValue('C'.$i, "'".$h['lf1']);
               $objActSheet->setCellValue('D'.$i, "'".$h['lf2']);
               $objActSheet->setCellValue('E'.$i, "'".$h['lf3']);
               $objActSheet->setCellValue('F'.$i, "'".$h['lf4']);
               $objActSheet->setCellValue('G'.$i, "'".$h['lf5']);
               $objActSheet->setCellValue('H'.$i, "'".$h['lf6']);
               $objActSheet->setCellValue('I'.$i, "'".$h['lf7']);
               $objActSheet->setCellValue('J'.$i, "'".$h['lf8']);
           }
       }
       //=SUM(C73:J81)/COUNT(C73:J81)
       $sum_m3=sprint_data(sum_array($fb2_array)/count($fb2_array),9);

       $objActSheet->getRowDimension(90)->setRowHeight(25);
       $objActSheet->setCellValue('A90', '');
       $objActSheet->setCellValue('B90', 'm3');
       $objActSheet->setCellValue('C90', $sum_m3);
       $objActSheet->setCellValue('D90', '');
       $objActSheet->setCellValue('E90', '');
       $objActSheet->setCellValue('F90', '');
       $objActSheet->setCellValue('G90', '');
       $objActSheet->setCellValue('H90', '');
       $objActSheet->setCellValue('I90', '');
       $objActSheet->setCellValue('J90', '');

       //92
       $objActSheet->setCellValue('A92', '');
       $objActSheet->setCellValue('B92', 'Lab');
       $objActSheet->setCellValue('C92', '1');
       $objActSheet->setCellValue('D92', '2');
       $objActSheet->setCellValue('E92', '3');
       $objActSheet->setCellValue('F92', '4');
       $objActSheet->setCellValue('G92', '5');
       $objActSheet->setCellValue('H92', '6');
       $objActSheet->setCellValue('I92', '7');
       $objActSheet->setCellValue('J92', '8');

       $objActSheet->getRowDimension(92)->setRowHeight(25);

       $objActSheet->getStyle('A92'.':J103')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
       $objActSheet->getStyle('A92:J103')->applyFromArray($styleArray);

       $lb1_array=[];
       if ($sjjylist) {
           $baseRow4 = 93; //数据从N-1行开始往下输出
           foreach ($sjjylist as $key => $h) {
               $i = $baseRow4 + $key;
               $lb1_array[]=$h['lb1'];
               $lb1_array[]=$h['lb2'];
               $lb1_array[]=$h['lb3'];
               $lb1_array[]=$h['lb4'];
               $lb1_array[]=$h['lb5'];
               $lb1_array[]=$h['lb6'];
               $lb1_array[]=$h['lb7'];
               $lb1_array[]=$h['lb8'];
               $objActSheet->getRowDimension($i)->setRowHeight(25);
               $objActSheet->setCellValue('A'.$i, '');
               $objActSheet->setCellValue('B'.$i, $h['nums']);
               $objActSheet->setCellValue('C'.$i, "'".$h['lb1']);
               $objActSheet->setCellValue('D'.$i, "'".$h['lb2']);
               $objActSheet->setCellValue('E'.$i, "'".$h['lb3']);
               $objActSheet->setCellValue('F'.$i, "'".$h['lb4']);
               $objActSheet->setCellValue('G'.$i, "'".$h['lb5']);
               $objActSheet->setCellValue('H'.$i, "'".$h['lb6']);
               $objActSheet->setCellValue('I'.$i, "'".$h['lb7']);
               $objActSheet->setCellValue('J'.$i, "'".$h['lb8']);

           }
       }
       //=SUM(C85:J93)/COUNT(C85:J93)
       $sum_m4=sum_array($lb1_array)/count($lb1_array);
       $objActSheet->getRowDimension(103)->setRowHeight(25);
       $objActSheet->setCellValue('A103', '');
       $objActSheet->setCellValue('B103', 'm4');
       $objActSheet->setCellValue('C103', $sum_m4);
       $objActSheet->setCellValue('D103', '');
       $objActSheet->setCellValue('E103', '');
       $objActSheet->setCellValue('F103', '');
       $objActSheet->setCellValue('G103', '');
       $objActSheet->setCellValue('H103', '');
       $objActSheet->setCellValue('I103', '');
       $objActSheet->setCellValue('J103', '');

       //

       $objActSheet->getStyle('A105'.':E107')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
       $objActSheet->getStyle('A105:E107')->applyFromArray($styleArray);

       $objActSheet->getRowDimension(105)->setRowHeight(25);
       $objActSheet->setCellValue('A105', '');
       $objActSheet->setCellValue('B105', '');
       $objActSheet->setCellValue('C105', '临界值（0.95,80）');
       $objActSheet->setCellValue('D105', '判断');
       $objActSheet->setCellValue('E105', '结论');

       //=ABS(C82)/SQRT(POWER(C70,3))
       $ptxsa=sprint_data(abs(floatval($sum_m3))/sqrt(pow(floatval($fb_m2),3)),4);
       if($ptxsa<0.43){
           $psxzt='正态分布';
       }else{
           $psxzt='有异常';
       }
       //=C94/POWER(C70,2)
       $ftxsb=sprint_data($sum_m4/pow($fb_m2,2),4);
       //2.27-3.87
       if($ftxsb>2.27 && $ftxsb<3.87){
           $ftxzt='正态分布';
       }else{
           $ftxzt='有异常';
       }

       $objActSheet->getRowDimension(106)->setRowHeight(25);
       $objActSheet->setCellValue('A106', '偏态系数A');
       $objActSheet->setCellValue('B106', $ptxsa);
       $objActSheet->setCellValue('C106', '0.43');
       $objActSheet->setCellValue('D106', 'A<A1');
       $objActSheet->setCellValue('E106', $psxzt);

       $objActSheet->getRowDimension(107)->setRowHeight(25);
       $objActSheet->setCellValue('A107', '峰态系数B');
       $objActSheet->setCellValue('B107', $ftxsb);
       $objActSheet->setCellValue('C107', '2.27-3.87');
       $objActSheet->setCellValue('D107', 'B1<B<B1');
       $objActSheet->setCellValue('E107', $ftxzt);


        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="数据检验10家（'. date('Y-m-d')  .'）'.'.xlsx"');
        header('Cache-Control: max-age=0');
        $objWriter->save('php://output');

    }
}